Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy past two existing sheets into 30 workbooks
I want to copy paste two existing sheets into 30 workbooks in a
folder. The sheets are in a workbook called Master, and I need to make a copy/ move and insert into each workbook within the folder. What is the simplest way to do this via VBA? Thanks, Robb |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy past two existing sheets into 30 workbooks
You will need something like this:
Sub dk() For Each wb In myFolder.Workbooks For i = 1 To 2 Workbooks("Master").Sheets(i).Copy + After:=wb.Sheets(wb.Sheets.Count) Next Next End Sub This is totally untested and is only to give guidance as to structure for creating a workable loop to go through the folder of files and copy worksheets to them. Some things that have to happen are, each workbook in the folder will have to be opened and then closed after the copy event. The myFolder variable will need to include path. "raffrobb" wrote in message ... I want to copy paste two existing sheets into 30 workbooks in a folder. The sheets are in a workbook called Master, and I need to make a copy/ move and insert into each workbook within the folder. What is the simplest way to do this via VBA? Thanks, Robb |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy past two existing sheets into 30 workbooks
I need to proof read these before entering. Change the + To _
You will need something like this: Sub dk() For Each wb In myFolder.Workbooks For i = 1 To 2 Workbooks("Master").Sheets(i).Copy _ After:=wb.Sheets(wb.Sheets.Count) Next Next End Sub This is totally untested and is only to give guidance as to structure for creating a workable loop to go through the folder of files and copy worksheets to them. Some things that have to happen are, each workbook in the folder will have to be opened and then closed after the copy event. The myFolder variable will need to include path. "JLGWhiz" wrote in message ... You will need something like this: Sub dk() For Each wb In myFolder.Workbooks For i = 1 To 2 Workbooks("Master").Sheets(i).Copy + After:=wb.Sheets(wb.Sheets.Count) Next Next End Sub This is totally untested and is only to give guidance as to structure for creating a workable loop to go through the folder of files and copy worksheets to them. Some things that have to happen are, each workbook in the folder will have to be opened and then closed after the copy event. The myFolder variable will need to include path. "raffrobb" wrote in message ... I want to copy paste two existing sheets into 30 workbooks in a folder. The sheets are in a workbook called Master, and I need to make a copy/ move and insert into each workbook within the folder. What is the simplest way to do this via VBA? Thanks, Robb |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy past two existing sheets into 30 workbooks
Hi Rob
This will open all the excel in a designated folder. You will need to change the file path to suit and also the names of the sheets to copy. This macro assumes you are running it from the Master spreadsheet. Hope this helps. Take care Marcus Sub CopytoXLFiles() Dim lCount As Long Dim wbResults As Workbook Dim wbThis As Workbook Dim dblValue As Double Dim WbCnt As Long Dim Wrkbook As String With Application On Error Resume Next Set wbThis = ThisWorkbook dblValue = 0 WbCnt = 0 With .FileSearch .NewSearch .LookIn = "C:\Documents and Settings\MS2642\MyXLFiles \Personal\FoldertoOpen\" '<-Change path to suit .FileType = msoFileTypeExcelWorkbooks If .Execute 0 Then 'Workbooks in folder For lCount = 1 To .FoundFiles.Count 'Loop through all. 'Open Workbook x and Set a Workbook variable to it Set wbResults = Workbooks.Open (Filename:=.FoundFiles(lCount), UpdateLinks:=0) Wrkbook = ActiveWorkbook.Name wbThis.Activate Sheets(Array("Test1", "Test2")).Copy Befo=Workbooks(Wrkbook).Sheets(1) wbResults.Activate WbCnt = WbCnt + 1 Next lCount End If End With End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy past two existing sheets into 30 workbooks
See also
http://www.rondebruin.nl/copy4.htm See the last example -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "raffrobb" wrote in message ... I want to copy paste two existing sheets into 30 workbooks in a folder. The sheets are in a workbook called Master, and I need to make a copy/ move and insert into each workbook within the folder. What is the simplest way to do this via VBA? Thanks, Robb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot copy & past beween workbooks | Excel Discussion (Misc queries) | |||
Formatting worksheets, existing and new, in existing workbooks | Excel Discussion (Misc queries) | |||
Copy and past to different sheets | Excel Discussion (Misc queries) | |||
inserting sheets into existing workbooks w/o external references | Excel Worksheet Functions | |||
Copy & Past from multiple sheets to one | Excel Programming |