VBA - Copying data from multisheet workbooks into sheet summaries

Hi guys,

I know there has been tonz of questions like this one. I still have not found the answer I need though.
I am attaching a code that should allow me to enter multiple workbooks where I want to select and copy specific ranges and paste it one next to each other in the sheet summary.
The workbooks always have the same structure and the worksheets in the same order:

Book 1 - sheet A, sheet B, sheet C, etc.
Book 2 - sheet A, sheet B, sheet C, etc.
Book 3 - sheet A, sheet B, sheet C, etc.

I want the code to select and copy a specific range within these sheets and paste it in the master summary in the following manner:

Master

                 A                                         B                                              C
1      data from Book 1 - Sheet  A      data from Book 2 - Sheet A       data from Book 2 - Sheet A

and I want the loop to do it for every sheet there is. The master has the sheets of the same name but obviously not the same structure ( as they are empty).

I cannot come up with the code myself, so I am turning to you to help me simplfy this creative peace I've written below.

Thank you for your inputs. I am sure it's simple ;)
Sub Copy_Paste()

    Dim wb As Workbook
    Dim objFileDLG As Office.FileDialog
    Dim strFilePath, lnLoop, lnLineNo, lcTargetCell
    
    
    Set objFileDLG = Application.FileDialog(msoFileDialogFilePicker)
    lnLoop = 1
    lnLineNo = 2
    
    Do While lnLoop < 6
        With objFileDLG
            .Filters.Add "Excel Files", "*.xlsx", 1
            .FilterIndex = 1
            .AllowMultiSelect = False
            .Title = "Select The Workbook to copy From "
            If .Show() <> 0 Then
                strFilePath = .SelectedItems(1)
            End If
        End With
        
        Set wb = Workbooks.Open(strFilePath)
        wb.Activate
        wb.Worksheets(1).Range("AH9:AH50").Copy
        Select Case lnLoop
            Case 1
                lcTargetCell = "B2"
            Case 2
                lcTargetCell = "C2"
            Case 3
                lcTargetCell = "D2"
            Case 4
                lcTargetCell = "E2"
            Case 5
                lcTargetCell = "F2"
            Case 6
                lcTargetCell = "G2"
            Case 7
                lcTargetCell = "H2"
            Case 8
                lcTargetCell = "I2"
            Case 9
                lcTargetCell = "J2"
            Case 10
                lcTargetCell = "K2"
            Case 11
                lcTargetCell = "L2"
            Case 12
                lcTargetCell = "M2"
            Case 13
                lcTargetCell = "N2"
            Case 14
                lcTargetCell = "I2"
            Case 15
                lcTargetCell = "J2"
            Case 16
                lcTargetCell = "K2"
            Case 17
                lcTargetCell = "L2"
            Case 18
                lcTargetCell = "M2"
            Case 19
                lcTargetCell = "N2"
            Case 20
                lcTargetCell = "O2"
            Case 21
                lcTargetCell = "P2"
            Case 22
                lcTargetCell = "Q2"
            Case 23
                lcTargetCell = "R2"
            Case 24
                lcTargetCell = "S2"
            Case 25
                lcTargetCell = "T2"
            Case 26
                lcTargetCell = "U2"
            Case 27
                lcTargetCell = "V2"
            Case 28
                lcTargetCell = "W2"
                
                         
        End Select
        
        ThisWorkbook.Worksheets(1).Activate
        Range(lcTargetCell).Activate
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                   xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        lnLoop = lnLoop + 1
        wb.Close
        Set wb = Nothing
        
        
       Loop
End Sub

Open in new window

lejohneyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

StephenJRCommented:
Can you post a sample of one of your workbooks?
0
lejohneyAuthor Commented:
Hi,

here is what I want to get done. Thanks in advance Samp.xlsm
0
StephenJRCommented:
I don't think I follow completely. I can't test, but see if this simple version is along the right lines. It only covers a single sheet "CLOOS -1323.003" and I'm not clear about where the output should go. See how it goes.
Sub Copy_Paste()

Dim wb As Workbook, vSheets, i As Long
Dim objFileDLG As Office.FileDialog
Dim strFilePath, lnLoop, lnLineNo, lcTargetCell

vSheets = Array("CLOOS -1323.003")
Set objFileDLG = Application.FileDialog(msoFileDialogFilePicker)
lnLoop = 1
lnLineNo = 2

Do While lnLoop < 6
    With objFileDLG
        .Filters.Add "Excel Files", "*.xlsx", 1
        .FilterIndex = 1
        .AllowMultiSelect = False
        .Title = "Select The Workbook to copy From "
        If .Show() <> 0 Then
            strFilePath = .SelectedItems(1)
        End If
    End With
    
    Set wb = Workbooks.Open(strFilePath)
    For i = LBound(vSheets) To UBound(vSheets)
        wb.Sheets(vSheets(i)).Range("AJ12:AJ35").Copy
        ThisWorkbook.Worksheets("SUMSHEET").Cells(13, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        Application.CutCopyMode = False
    Next i
    lnLoop = lnLoop + 1
    wb.Close
    Set wb = Nothing
Loop

End Sub

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

lejohneyAuthor Commented:
Hi Stephen JR,

thanks for the code. I need the output to go to a seperate summarization workbook, let's call it SUMVALUES.xlsx. In this sample case I need the workbook to have the sheets named the same way as they are in the sample file. Now, the sample file is only 1 out of 30 such files I need to summarize the range from this 30 workbooks for each sheet into the SUMVALUES.xlsx. I hope it's clearer and you will be able to adjust the code henceforward. I thank you very much again!
0
StephenJRCommented:
Do we make progress if we change just line 26 above to

ThisWorkbook.Worksheets(vSheets(i)).Cells(13, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Open in new window

?

The code goes in your summary workbook.

0
lejohneyAuthor Commented:
Hi Stephen,

actually this does not resolve the whole problem, it only pastes the values from one of the sheets, not from all (in this case three) sheets that are in the raw data workbook. Could we make the code to loop through the workbook and take the same range from every sheet and paste it to individual sheets that are named the same way in source workbook and the summary workbook? The code now only searches in the first sheet every time. I hope I am clear. Thank you very much for your help!
0
StephenJRCommented:
OK, try expanding the array in line 7, separated by commas, e.g.
vSheets = Array("CLOOS -1323.003","Fred","George")

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.