Copy worksheet to multiple workbooks

[belldog]

Greetings,

I have a spreadsheet that I need to copy, as a new tab, to about 160 workbooks. Is there a quick way to do this?

Thanks in advance for any help.

belldog

 

[leecavturbo]

got my answer by trying it so yes however many workbooks there are in the folder it will process.
however i have an issue. how can i get cells in the pasted sheet to reference cells in sheet 1 of the destination workbook?
i tried to reference a cell but i get a "this wookbook contains links to other data sources" message! ??
with option to update or not. picking update ends in error with cannot be updated.

surely a simple cell =sheet1!a1 will reference its own workbook?

 

[Sam666666]

Is there a quick way of copying the same sheet in one workbook 160 times?

 

[rkobeyer]

got my answer by trying it so yes however many workbooks there are in the folder it will process.
however i have an issue. how can i get cells in the pasted sheet to reference cells in sheet 1 of the destination workbook?
i tried to reference a cell but i get a "this wookbook contains links to other data sources" message! ??
with option to update or not. picking update ends in error with cannot be updated.

surely a simple cell =sheet1!a1 will reference its own workbook?

I would also like to know how to do this! It would save me days of work:

To have the charts within a worksheet copied from one workbook to another, to reference the destination workbook cells.

But instead the charts references the original workbook. Everywhere I have looked this seems impossible for charts.

If copying normal cells with formulas from one workbook to another there's the workaround where you change "=" in the formula to "#", then after copy/paste you just change "#" back to "=". But with charts things are completely different.

Anyone got any ideas?

 

[AsparagusLady]

Good morning,

I am trying to copy a single sheet to 219 different workbooks. I tried this code yesterday on a coupel of empty test workbooks (completely new to VB) and it worked perfectly.....this morning I'm trying to run it for real but I'm getting a couple of error messages ..... at the moment 'Run-time error 9 : Subscript out of range'.

Any advice would be much appreciated!

Option Explicit
Public Sub CopySheetToAllWorkbooksInFolder()
Dim sourceSheet As Worksheet
Dim folder As String, filename As String
Dim destinationWorkbook As Workbook

'Worksheet in active workbook to be copied as a new sheet to the 219 workbooks

Set sourceSheet = ActiveWorkbook.Worksheets("16-17")

'Folder containing the 219 workbooks

folder = "\\ash-dc01\FolderRedirection\DHendricksen\Desktop\MacroTest\"

filename = Dir(folder & "*.xls", vbNormal)
While Len(filename) <> 0
Debug.Print folder & filename
Set destinationWorkbook = Workbooks.Open(folder & filename)
sourceSheet.Copy before:=destinationWorkbook.Sheets(1)
destinationWorkbook.Close True
filename = Dir() ' Get next matching file
Wend
End Sub


TIA

 

[Perpa]

TIA,
You should probably post this to a new thread, but your issue looks pretty simple...
It looks like you have tried to put more than one sheet in the 'Set sourceSheet' line.
Try the following (changes in red). If that doesn't resolve this you should make a new post refering to the current thread.
http://www.mrexcel.com/forum/excel-questions/399744-copy-worksheet-multiple-workbooks-2.html
Perpa

Option Explicit
 Public Sub CopySheetToAllWorkbooksInFolder()
 Dim sourceSheet As Worksheet
 Dim folder As String, filename As String
 Dim destinationWorkbook As Workbook
 [COLOR=#ff0000]Dim nm as Integer[/COLOR]
 'Worksheet in active workbook to be copied as a new sheet to the 219 workbooks

[COLOR=#ff0000]For nm = 16 to 17[/COLOR]
Set sourceSheet = ActiveWorkbook.Worksheets("Sheet[COLOR=#ff0000]" & nm[/COLOR])
folder = "[URL="file://\\ash-dc01\FolderRedirection\DHendricksen\Desktop\MacroTest\"]\\ash-dc01\FolderRedirection\DHendricksen\Desktop\MacroTest\[/URL]"
 filename = Dir(folder & "*.xls", vbNormal)
 While Len(filename) <> 0
 Debug.Print folder & filename
 Set destinationWorkbook = Workbooks.Open(folder & filename)
 sourceSheet.Copy before:=destinationWorkbook.Sheets(1)
 destinationWorkbook.Close True
 filename = Dir() ' Get next matching file
 Wend
[COLOR=#ff0000]Next nm[/COLOR]
End Sub

 

[Saravavan]

Hi, Thanks much, I too was in need of this solution. Also please help with the code, if the excel files are in subfolders how do we copy a sheet.

This current code works if the excel files are in a folder, in my case if the excel files are in sub folders, how do we copy a sheet, please help

 

[Perpa]

Hi, Thanks much, I too was in need of this solution. Also please help with the code, if the excel files are in subfolders how do we copy a sheet.

This current code works if the excel files are in a folder, in my case if the excel files are in sub folders, how do we copy a sheet, please help

Saravavan,
Welcome to the Forum.
You really need to start a new thread for your question and refer to this post 5112276 by providing the link:


https://www.mrexcel.com/forum/excel...rksheet-multiple-workbooks-2.html#post5112276


That being said, I did a quick search using the search tool (upper RH corner of Mr Excel page) using the search string 'copy sheet to subfolders' and found a couple of posts that were trying to do something similar:


'Copy worksheet to multiple workbook in folder and subfolders'
https://www.mrexcel.com/forum/excel...lders.html?highlight=copy+sheet+to+subfolders
Have a look at Post #3 by Tinbendr


Another was...
'Loop through subfolders'
https://www.mrexcel.com/forum/excel...lders.html?highlight=copy+sheet+to+subfolders


See if these don't get you going in the right direction. If not, then try starting a new post of your own.
Good luck.
Perpa

 

[svanslyck]

I've found that the workbook from which the code runs must not be in the target directory. At least not today (because it seemed to work fine yesterday).