Copy data from one workbook to multiple workbooks

[pkdash83]

Hi Guys,

I'm trying to copy data from a sheet in a workbook to multiple workbooks.

I've tried the following code to copy the data to one file, but don't know how to loop through and apply to multiple files. I need to copy the data from Source workbook to Destination1/2/3 and so on workbooks

Open to any suggestion.

Can you please help?

Sub Copy_Data()
Dim src As Workbook
Dim dest As Workbook
Set src = Workbooks.Open("F:\Excel Workshop\Source.xlsx")
Set dest = Workbooks.Open("F:\Excel Workshop\Destination1.xlsx")
src.Worksheets("Sales").Range("A1:B6").Copy
dest.Worksheets("Sheet1").Range("A1.B6").PasteSpecial (xlPasteValues)
ActiveWorkbook.Save
Workbooks("Source.xlsx").Close
Workbooks("Destination1.xlsx").Close
End Sub

Thanks,
Prasanna

 

[pkdash83]

Hi Guys,

Can you please point out the error in the below code? This is in continuation of my previous post.

Sub Copy_Data()Dim i As Integer
Dim src As Workbook
Dim dest As Workbook
Dim mypath As String
Dim myfile As String
mypath = "F:\Excel Workshop\"
myfile = "Source.xlsx"
Set src = Workbooks.Open(FileName:=mypath & myfile)


For Each mydest In Workbooks


For i = 1 To 20


Set dest = Workbooks.Open("F:\Excel Workshop\Destination\Destination&(i).xlsx")
src.Worksheets("Sales").Range("A1:B6").Copy
dest.Worksheets("Sheet1").Range("A1.B6").PasteSpecial (xlPasteValues)


Next i
Next mydest


ActiveWorkbook.Save
src.Close
dest.Close
End Sub

Thanks,
Prasanna