VBA Code to copy specific header name columns and paste it another wb

[FaizanRoshan88]

I have code to select source wb and then copy ws to Main WB. I want to change code for selecting source wb & then ws, then code find special header name and copy entire specific columns data form sheet to Main wb. Header Name is "Name, Type, Date, Num, Item, Qty, Sale Price & Amount"

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.xlsx (*.xlsx), *.xlsm (*.xlsm),*.xls (*.xls),")

If FileToOpen = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
Else
    Set wb2 = Workbooks.Open(fileName:=FileToOpen)

    For Each Sheet In wb2.Sheets
        With Sheet.UsedRange
            .Copy PasteSpecialFormatsStart
            Set PasteSpecialFormatsStart = PasteSpecialFormatsStart.Offset(.Rows.Count)

 

[Monty]

Hello FaizanRoshan88.

Please find attached...

Let me know any changes..Happy to help you

 

[FaizanRoshan88]

Dear, i have not found any modification in file, Please let me know if you have done any change in file as i like to change code.

 

[Monty]

Hey...

i missed the code you wanted to select the file with specific sheet to process...hold on.

 

[FaizanRoshan88]

Hey...

i missed the code you wanted to select the file with specific sheet to process...hold on.

Yes, I attached both file, you will see old code in Module "OpenFile"

After select specific sheet then code find special header name and copy entire specific columns data form source ws to wb ("Create Invoice").ws("RawData"). Header Name will be ("Name, Type, Date, Num, Item, Qty, Sale Price, Amount")
Note: (if possible)
if above header data have in 2 different sheet in source wb then option to select both sheets ("ws1, ws2") & it paste both ws data in ws("Rawdata")

 

[Monty]

FaizanRoshan88

One quick question..As per you code which is build which is really good.

So when you click on the button "Upload file" you wanted not only selecting file name but also sheet name..

Please clarify.

 

[FaizanRoshan88]

FaizanRoshan88

One quick question..As per you god which is build which is really good.

So when you click on the button "Upoload file" you wanted not only selecting file name but also sheet name..

Please clarify.

Actually i export report for qb and then create Customer Invoices etc. through this vba file. in quickbook export many report sheet in one file. This code copy data form sheet one by one and overlap in RawData File. As source file have many type of data sheet so that i need each sheet data for different type of invoice and reporting. This reason i like to select sheet so that select sheet for data that i need to modify that time.

 

[Monty]

Hello Faizan.


Please find below code which will help you.


Here you can just use an input box to have the user write the name of the tab or you can launch the file dialog box from a userform (the better option).

Assuming you use a userform you can load the tab names into a drop down box and have the user select which tab it will be. Then do your code.

Another alternative is loading the tabs into your main workbook and have the user select it from there.

dim fd as filedialog
dim ffs as filedialogfilters
dim stFileName as string
dim wkb as workbook

set fd=application.filedialog(msofiledialogopen)
with fd
  set ffs=.filters
  with ffs
    .clear
    .add "Excel", "*.xls" 'Or whatever version you are using.
  end with
  .allowmultiselect=false
  if .show=false then exit sub
  set wkb=getobject(.selecteditems(1))
end with