Copy and paste data from the next Excel sheet VBA

Question

I'm a complete beginner with VBA and I looked at other topics but couldn't really find an answer (at least that I could understand).

Basically I have Excel workbooks for different subjects during a research project.

I want to copy past some data from the subjects workbook and past it to a master Workbook. This code does it for one subject.

It open the selected workbook, copy past what is needed in the right place then close it then open the next condition etc.

I'd like to find a way to tell it to open the next workbook (ie: Patient1GlobalP.xlsm becomes Patient2GlobalP.xlsm, then 3 then 4...) and do the same but copy to the next cell (the range that is copied stay the same but Range("C2").Select becomes Range("D2").Select for the copy destination, C1509 becomes D1509 and C3016 becomes D3016 then E then F...). I can do it by hand but there has to be a more elegant solution like some kind of offset.

Windows("Patient1GlobalP.xlsm").Activate
    Range("B1:B1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Ankle X").Select
    Range("C2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("Patient1GlobalP.xlsm").Activate
    Range("C1:C1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Ankle Y").Select
    Range("C2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("Patient1GlobalP.xlsm").Activate
    Windows("Patient1GlobalP.xlsm").Activate
    Range("C1:C1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Ankle Z").Select
    Range("C2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("Patient1GlobalP.xlsm").Activate
    Range("N1:N1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Knee X").Select
    Range("C2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
        Windows("Patient1GlobalP.xlsm").Activate
    Range("O1:O1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Knee Y").Select
    Range("C2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
        Windows("Patient1GlobalP.xlsm").Activate
    Range("P1:P1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Knee Z").Select
    Range("C2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
        Windows("Patient1GlobalP.xlsm").Activate
    Range("I1:I1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Hip X").Select
    Range("C2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("Patient1GlobalP.xlsm").Activate
    Range("J1:J1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Hip Y").Select
    Range("C2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("Patient1GlobalP.xlsm").Activate
    Range("J1:J1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Hip Z").Select
    Range("C2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
Workbooks.Open Filename:="C:\Users\hugo\Desktop\data\Patient1LocalP.xlsx"
    Windows("Patient1LocalP.xlsx").Activate
    Range("B1:B1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Ankle X").Select
    Range("C1509").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("Patient1LocalP.xlsx").Activate
    Range("C1:C1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Ankle Y").Select
    Range("C1509").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("Patient1LocalP.xlsx").Activate
    Windows("Patient1LocalP.xlsx").Activate
    Range("C1:C1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Ankle Z").Select
    Range("C1509").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("Patient1LocalP.xlsx").Activate
    Range("N1:N1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Knee X").Select
    Range("C1509").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
        Windows("Patient1LocalP.xlsx").Activate
    Range("O1:O1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Knee Y").Select
    Range("C1509").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
        Windows("Patient1LocalP.xlsx").Activate
    Range("P1:P1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Knee Z").Select
    Range("C1509").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
        Windows("Patient1LocalP.xlsx").Activate
    Range("I1:I1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Hip X").Select
    Range("C1509").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("Patient1LocalP.xlsx").Activate
    Range("J1:J1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Hip Y").Select
    Range("C1509").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("Patient1LocalP.xlsx").Activate
    Range("J1:J1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Hip Z").Select
    Range("C1509").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("Patient1LocalP.xlsx").Activate
    ActiveWindow.Close
    Workbooks.Open Filename:="C:\Users\hugo\Desktop\data\Patient1Nopert.xlsx"
    Windows("Patient1Nopert.xlsx").Activate
    Range("B1:B1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Ankle X").Select
    Range("C3016").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("Patient1Nopert.xlsx").Activate
    Range("C1:C1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Ankle Y").Select
    Range("C3016").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("Patient1Nopert.xlsx").Activate
    Windows("Patient1Nopert.xlsx").Activate
    Range("C1:C1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Ankle Z").Select
    Range("C3016").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("Patient1Nopert.xlsx").Activate
    Range("N1:N1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Knee X").Select
    Range("C3016").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
        Windows("Patient1Nopert.xlsx").Activate
    Range("O1:O1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Knee Y").Select
    Range("C3016").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
        Windows("Patient1Nopert.xlsx").Activate
    Range("P1:P1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Knee Z").Select
    Range("C3016").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
        Windows("Patient1Nopert.xlsx").Activate
    Range("I1:I1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Hip X").Select
    Range("C3016").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("Patient1Nopert.xlsx").Activate
    Range("J1:J1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Hip Y").Select
    Range("C3016").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("Patient1Nopert.xlsx").Activate
    Range("J1:J1505").Select
    Selection.Copy
    Windows("master.xlsx").Activate
    Sheets("Hip Z").Select
    Range("C3016").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("Patient1Nopert.xlsx").Activate
    ActiveWindow.Close

Answer 1

Here's one possible approach:

Sub Tester()

    Const SRC_FLDR As String = "C:\something\myfiles\"

    Dim wbM As Workbook, wbS As Workbook, shtS As Worksheet
    Dim i As Long, colOff As Long

    Set wbM = Workbooks("master.xlsx")

    colOff = 0 'paste position offset

    For i = 1 To 10 'eg
        Set wbS = Workbooks.Open(SRC_FLDR & "Patient" & i & "GlobalP.xlsm")
        Set shtS = wbS.Sheets(1)

        'copy ranges to specific positions on Master sheets
        '  offset changes each time through the loop
        'NOTE: no need to use Activate/Select here
        shtS.Range("B1:B1505").Copy wbS.Sheets("Ankle X").Range("C2").Offset(0, colOff)
        shtS.Range("C1:C1505").Copy wbS.Sheets("Ankle Y").Range("C2").Offset(0, colOff)
        shtS.Range("C1:C1505").Copy wbS.Sheets("Ankle Z").Range("C2").Offset(0, colOff)
        'etc...
        'etc...

        wbS.Close savechanges:=False
        colOff = colOff + 1

    Next i

End Sub