How to copy-paste from one specific workbook to multiple workbook with different names?

Question

Sub Macro1()
'
' Macro1 Macro
'

'
    Windows("Formats.xlsx").Activate
    Range("A1:D3").Select
    Selection.Copy
    Windows("ABCD.xlsx").Activate     `need correction here, because I have to paste it on different workbooks with multiple sheets`
    Range("D3").Select
    ActiveSheet.Paste
End Sub

Hello, I am trying to copy a range ("A1:D3") from one workbook to multiple workbooks (different names) in which contains multiple sheets (different names). Can anyone help me on this matter? Thanks in advance.

Store the destination workbook/worksheet names in a 2D array. If you already have those in an excel range then that is also fine. Simply loop through the range/array and paste the data in them. Also there is no need to Activate/Select to copy paste. You may want to see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Siddharth Rout, Dec 24 '22 at 06:44

score 1 · Answer 1 · answered Dec 24 '22 at 10:21

Copy Range to Multiple Worksheets in Multiple Workbooks

Macro-Recorder

To receive a useful answer, you need to let us know the necessary details. The macro-recorder is a great tool but it's not perfect i.e. it often doesn't behave as we want it to.
In this particular case, your code doesn't tell us where the files are located because you didn't record this part. It doesn't tell us the worksheet names because they are the active ones. It doesn't tell us to save and close the workbooks because you didn't record this part. To work around this, you need to properly record the macro and manually add a few basic lines (see the comments in the code (manually added)).
If you look at the macro-recorder code, you will notice that almost all the necessary information to write a proper code is in it and any newb could understand most, if not all of it. Additionally, you should explain what the code doesn't show e.g. you want it to work for all files in the folder.

Proper Code Benefits

The proper code will loop through all .xlsx worksheets (not just two specific) in the folder.
It won't select anything it will just reference the objects (workbook, worksheet, range...) making it more efficient (faster).
Also, by using constants at the beginning of the code, it is quite easy to modify the various values and make it work for a similar yet different scenario.

Macro-Recorder Code

Sub Macro1()
'
' Macro1 Macro
'

'
' How to Record a Useful Macro
    
    ' 1. Close all related files.
    ' 2. Start recording.

    ' 3. Open the Source file (Ctrl+O),...
    Workbooks.Open Filename:="C:\Test\Formats.xlsx"
    ' ... select the worksheet,...
    Sheets("Sheet1").Select ' manually added
    ' ... select the range,...
    Range("A1:D3").Select
    Application.CutCopyMode = False ' automatically added before copy
    ' ... and copy the range (Ctrl+C).
    Selection.Copy
    
    ' 4. Open the 1st Destination file (Ctrl+O),...
    Workbooks.Open Filename:="C:\Test\MyWorkbooks\ABCD.xlsx"
    ' ... select the 1st worksheet,...
    Sheets("Sheet1").Select ' manually added
    ' ... select the first cell...
    Range("D3").Select
    ' ... and paste (Ctrl+V),...
    ActiveSheet.Paste
    ' ... select the 2nd worksheet,...
    Sheets("Sheet2").Select
    ' ... select the first cell,...
    Range("D3").Select
    ' ... paste (Ctrl+V),...
    ActiveSheet.Paste
    ' ... save the file (Ctrl+S)...
    ActiveWorkbook.Save
    ' ... and close it (Ctrl+W).
    ActiveWorkbook.Close
    
    ' 4. Open the 2nd Destination file (Ctrl+O),...
    Workbooks.Open Filename:="C:\Test\MyWorkbooks\EFGH.xlsx"
    ' ... select the 1st worksheet,...
    Sheets("Sheet1").Select ' manually added
    ' ... select the first cell...
    Range("D3").Select
    ' ... and paste (Ctrl+V),...
    ActiveSheet.Paste
    ' ... select the 2nd worksheet,...
    Sheets("Sheet2").Select
    ' ... select the first cell,...
    Range("D3").Select
    ' ... paste (Ctrl+V),...
    ActiveSheet.Paste
    ' ... save the file (Ctrl+S)...
    ActiveWorkbook.Save
    ' ... and close it (Ctrl+W).
    ActiveWorkbook.Close
    
    ' 5. Activate the Source file...
    Windows("Formats.xlsx").Activate ' manually added
    ' ... and close it (Ctrl+W).
    ActiveWorkbook.Close

    ' 6. Stop recording.

End Sub

Proper Code

Sub CopyRange()
    
    ' Define constants.
    Const SRC_FILE_PATH As String = "C:\Test\Formats.xlsx"
    Const SRC_WORKSHEET As String = "Sheet1"
    Const SRC_RANGE As String = "A1:D3"
    Const DST_FOLDER_PATH As String = "C:\Test\MyWorkbooks\"
    Const DST_FIRST_CELL As String = "D3"
    Const DST_PATTERN As String = "*.xlsx"
    Dim dSheetNames(): dSheetNames = Array("Sheet1", "Sheet2")
    
    ' Reference the source range ('srg').
    Dim swb As Workbook: Set swb = Workbooks.Open(SRC_FILE_PATH)
    Dim sws As Worksheet: Set sws = swb.Sheets(SRC_WORKSHEET)
    Dim srg As Range: Set srg = sws.Range(SRC_RANGE)
        
    ' Get the first Destination file name.
    Dim dFileName As String
    dFileName = Dir(DST_FOLDER_PATH & "*" & DST_PATTERN)
        
    Application.ScreenUpdating = False

    Dim dwb As Workbook, dws As Worksheet, dfCell As Range, dSheetName
    
    ' Open each file in the folder and copy the Source range
    ' to the worksheets from the list (array i.e. 'dSheetNames').
    ' and save and close it.
    Do While Len(dFileName) > 0
        Set dwb = Workbooks.Open(DST_FOLDER_PATH & dFileName)
        For Each dSheetName In dSheetNames
            Set dws = dwb.Sheets(dSheetName)
            Set dfCell = dws.Range(DST_FIRST_CELL)
            srg.Copy dfCell
        Next dSheetName
        dwb.Close SaveChanges:=True
        dFileName = Dir ' next file
    Loop
        
    Application.ScreenUpdating = True

    ' Inform.
    MsgBox "Range copied.", vbInformation

End Sub

How to copy-paste from one specific workbook to multiple workbook with different names?

1 Answers1

Copy Range to Multiple Worksheets in Multiple Workbooks