Copy data from one workbook to multiple workbooks

pkdash83

New Member
Joined
Apr 28, 2014
Messages
12
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?


Code:
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hi Guys,

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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,226,729
Messages
6,192,696
Members
453,747
Latest member
tylerhyatt04

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top