How to Copy data from Source to multiple Destination File if Macros code is in Source file?

Question

Suppose I have Source.xlsm( it has only one sheet file) ,it has data in that sheet. and I have 10 different destination.xlsx file in that we have multiple sheets suppose abc, efg, ijk. I have to copy whole data present in Source.xlsx file to 10 different destination excel (under sheet_name ijk). I want to have macro code in source.xlsm when i click on the button it should copy whole data to 10 different destination files(under sheet_name ijk).

stuck for days please help me

    Private Sub CommandButton1_click()
Dim wb As Workbook
Dim lRow As Long
Dim lcol As Long
Dim total As String

ThisWorkbook.Worksheets("ViewList").Select
lRow = Cells(Rows.Count, 1).End(xlUp).Row
lcol = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(1, 1), Cells(lRow, lcol)).Copy




Set wb = Workbooks.Open("C:\Users\Desktop\Destination.xlsx")
wb.Worksheets("abc").Activate

ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close savechanges = True
ThisWorkbook.Worksheets("ViewList").Activate
ThisWorkbook.Worksheets("ViewList").Cells(1, 1).Select
Application.CutCopyMode = False




End Sub

This code is pasting data in the middle of destination file i want to paste it in from cell (1,1)

Answer 1

I recommend the following

Private Sub CommandButton1_click()
    Dim wsViewList As Worksheet
    Set wsViewList = ThisWorkbook.Worksheets("ViewList")

    Dim lRow As Long
    lRow = wsViewList.Cells(wsViewList.Rows.Count, 1).End(xlUp).Row

    Dim lcol As Long
    lcol = wsViewList.Cells(1, wsViewList.Columns.Count).End(xlToLeft).Column

    Dim wb As Workbook
    Set wb = Workbooks.Open("C:\Users\Desktop\Destination.xlsx")

    wsViewList.Range("A1", wsViewList.Cells(lRow, lcol)).Copy Destination:=wb.Worksheets("abc").Range("A1").Paste
    
    'wb.Save 'this statement is not needed because you save on closing the workbook. Otherwise you would save twice which takes twice the time.
    wb.Close SaveChanges:=True
    wsViewList.Cells(1, 1).Select 'this is actually not needed unless you want to move the users view to that cell. If that is not what you need remove that line.
    Application.CutCopyMode = False
End Sub

Your code would work without any .Select or .Activate statements. Also your code needs to know where exactly you want to paste so you should specify the destination cell not only the worksheet. Also it is a good practice do do the copy and past in one statemant or at least don't do any further steps between copy and paste because that can interfere with the copyied range.

Finally named parameters need to be submitted with := not with = sign. I highly recommend you to activate Option Explicit because the line

ActiveWorkbook.Close savechanges = True

does actually do the oposite of what you think:

1. savechanges = True because of the missing := sees savechanges as an undeclared variable of type Variant and compares if this is True. Since it is not declared and not initialized with any value the result of this statement is False.
2. Finally you submit that result False as first parameter to ActiveWorkbook.Close so it is the same as writing ActiveWorkbook.Close False. So what your code actually does is it closes the workbook without saving changes.

If you used Option Explicit it would have notified you that savechanges is an undeclared variable. This way this fault would have been prevented. Without that notification it is much harder to see and find that issue.

Therefore I recommend always to activate Option Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration.