从下一个Excel工作表VBA复制并粘贴数据

时间：2022-11-20 07:40:21

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).

我是VBA的初学者,我看过其他主题但却找不到答案(至少我能理解)。

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

基本上我在研究项目中有不同科目的Excel工作簿。

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.

我想找个方法告诉它打开下一个工作簿(即:Patient1GlobalP.xlsm变为Patient2GlobalP.xlsm,然后是3然后4 ......)并执行相同操作但复制到下一个工作簿(范围是复制保持相同但范围(“C2”)。选择变为范围(“D2”)。选择复制目的地,C1509变为D1509,C3016变为D3016,然后是E,然后是F ......)。我可以手工完成,但必须有更优雅的解决方案,如某种偏移。

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

1 个解决方案

#1

0  

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

#1

0  

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
标签：

相关文章