Pasting to concatenated named workbooks

[XlDad23]

Hi I am a newbie to VBA and trying to accomplish a project where I create new folders copy template files into said folders, rename the files and copy data into the files. I am doing this from names and filepaths concatenated into cells in a master workbook. I have manged to create the folders,copy the files and rename them, the part I am struggling with is copying the data from the master workbook into the created files. I do have some code (and I can manage to open the workbooks) it appears I can't set the destination workbook for the copy paste routine. All the filepaths are in 'P' column, the filenames inc. extension are in 'O' column. Any help where I am going wrong would be greatfully appreciated. Here is the code I have.

Sub copyToMultipleSheets()

Application.ScreenUpdating = False

Dim sourceWB As Workbook
Dim DestWb As Workbook
Dim I As Integer
For I = 5 To 14
Set sourceWB = ThisWorkbook

Set DestWb = Workbooks(ActiveWorkbook.Sheets(Sheet1).Range("O" & I).Value)

Workbooks.Open Filename:=Range("P" & I).Value, UpdateLinks:=0

ThisWorkbook.Worksheets("Sheet1").Range("C2").copy Filename:=Range("P" & I).Value.Sheets("Bucket 1").Range("L2")
'ThisWorkbook.Worksheets("Sheet1").Range("C" & I).copy Destination:=destWB.Sheets("Summary").Range("D6:D10")
'ThisWorkbook.Worksheets("Sheet1").Range("D" & I).copy Destination:=destWB.Sheets("Summary").Range("G6:G10")
'ThisWorkbook.Worksheets("Sheet1").Range("E" & I).copy Destination:=destWB.Sheets("Summary").Range("F12")

Application.CutCopyMode = False
'destWB.Save
'destWB.Close
Next
Application.ScreenUpdating = True
End Sub

 

[Saurabhj]

Hi,

1. Please share the sheet data also to check how you are showing file path and name.
2. In below code Sheet1 should be in double quotes, if sheet1 is the sheet name.

Set DestWb = Workbooks(ActiveWorkbook.Sheets("Sheet1").Range("O" & I).Value)

3. In below code only file path reference is given. File name is missing. Reference of Destination Workbook is missing.

ThisWorkbook.Worksheets("Sheet1").Range("C2").copy Filename:=DestWb.Sheets("Bucket 1").Range("L2")

 

[XlDad23]

Thanks for your help.I tried both with and without quotes on the sheet name. It does seem to be the setting the DestWb that it doesn't like as when i get rid of that line it will open the workbook. The numbers in the K col are for the drop down for the week no. which I realize could be done a different way.