multiple open workbooks' data copy and paste to one workbook based on reference match

[autumnK]

hello. I am new to this forum as much as I am new to Excel VBA.

Basically, I am trying to do copy and paste data range to 'already formatted excel file with lots of tabs'. I could do this with manually but problem is this is really time consuming and tedious job. When I export excel data files from a certain program, I have lots of series of new workbooks with only sheet1 has data in it. (eg. book1~book 12, each book has 3sheets, but only "sheet1" has a data stored).

Formatted excel file (A.K.A reporting file) already created tabs with names, and based on the data I paste, it connected with other formulas and graphs as well.

I want to reference check with both Template ID("D9") and Projection Name("D6") from all extracted data with reporting excel file. If the data has same Template ID and Projection Name, then I would like to copy Range "B2:fg373" and paste in the matching reference tab.

Please help me how to start, I only know how to select range, copy and paste, but this one needs to right directory, which where it needs to be selected, copied, and pasted.

Thanks for your help.

Anything would be good for me to start.

Autumn

 

[tonyyy]

Autumn,

Welcome to the Board.

The following might get you started...

Sub AnotherMaster()
Application.ScreenUpdating = False
Dim wb As Workbook, wb2 As Workbook
Dim FolderName As String, fileName As String
Dim i As Long, NextRow As Long

''''Select folder that contains files
With Application.FileDialog(msoFileDialogFolderPicker)
  .AllowMultiSelect = False
  If .Show = 0 Then Exit Sub
  FolderName = .SelectedItems(1) & "\"
End With

Set wb = ThisWorkbook 'workbook with this macro
fileName = Dir(FolderName & "*.xls?")

''''Loop through files
Do While fileName <> ""
    If fileName <> wb.Name Then
        Set wb2 = Workbooks.Open(FolderName & fileName)
            'If/Then statement
            'Copy/Paste statement
        wb2.Close savechanges:=False
    End If
    fileName = Dir
Loop
Application.ScreenUpdating = True
MsgBox "The dishes are done, dude!"
End Sub

The code will open a file dialog and prompt you to select the folder in which your data files reside. It will then proceed to loop through each workbook.

Unfortunately, the code is incomplete as these parts of your description aren't clear to me:

I want to reference check with both Template ID("D9") and Projection Name("D6") from all extracted data with reporting excel file.

Template ID and Projection Name are worksheets in the reporting file?

If the data has same Template ID and Projection Name, then I would like to copy Range "B2:fg373" and paste in the matching reference tab.

What cells are we checking in the data file that would contain the Template ID and Projection Name? And where is the tab name referenced in the data file?

Cheers,

tonyyy

 

[autumnK]

Dear tonyyy

Thank you so much for your replying! You must be really good at VBA!. I am so new to this VBA work, I don't even know it's capability, how much vba can do.

I am so sorry for the confusion. I think wrote down the description very poorly. Honestly, I also don't know how to explain in right language.
Anyway, but for clarifying your question, let me add some more.

So.. Template ID("D9") and Projection Name("D6") is in both reporting excel file and also export excel file as well. Also both those are inside the range I wanted to copy too. When I exporting lots of data files from a certain program, it produces several new workbooks(e.g. "book1.xlsm"~~ ""book#n.xlsm")
I need to consolidate it's each book's sheet 1 into reporting excel file. I thought only way to automate this process is check template ID and projection name.
If reporting file's one tab has a same projection name and template ID from one of the exported excel file, then it copy and paste it. That is what I originally thought.

For your question #2, Reporting file's tab name is not associated with data it self. I gave abbreviate name based on the data so that I think it is not a good way to use for this job.

Thank you so much for your help! tonyyy


Autumn

 

[autumnK]

By the way,

exported data won't be saved because there are so many, that is why I think it would be nice to just find same template ID and Projection Name in both exported data and reporting excel file then copy&paste.

Sorry for the confusion again.

I also don't even know how to start for this work.

Autumn

 

[tonyyy]

Questions...

So if D9 and D6 on Sheet1 of the reporting file match D9 and D6 on Sheet1 of the export file, then copy B2:FG373 from the export file to the reporting file? To which sheet in the reporting file?

 

[autumnK]

Yes! Basically that is what I want!

sounds simple, but there are lots of tabs in reporting file. and lots of different export files....

 

[tonyyy]

Guessing that you want to loop through the sheets in the reporting file until D9 and D6 match sheet1 in the export file...

Sub AnotherMaster()
Application.ScreenUpdating = False
Dim wb As Workbook, wb2 As Workbook
Dim FolderName As String, fileName As String
Dim i As Long, NextRow As Long
Dim ws As Worksheet

''''Select folder that contains files
With Application.FileDialog(msoFileDialogFolderPicker)
  .AllowMultiSelect = False
  If .Show = 0 Then Exit Sub
  FolderName = .SelectedItems(1) & "\"
End With

Set wb = ThisWorkbook 'workbook with this macro
fileName = Dir(FolderName & "*.xls?")

''''Loop through files
Do While fileName <> ""
    If fileName <> wb.Name Then
        Set wb2 = Workbooks.Open(FolderName & fileName)
            For Each ws In wb.Worksheets
                If ws.Range("D9") = wb2.Sheets(1).Range("D9") And ws.Range("D6") = wb2.Sheets(1).Range("D6") Then
                    wb2.Sheets(1).Range("B2:FG373").Copy Destination:=ws.Range("A1")
                End If
            Next ws
        wb2.Close savechanges:=False
    End If
    fileName = Dir
Loop
Application.ScreenUpdating = True
MsgBox "The dishes are done, dude!"
End Sub