copy past two existing sheets into 30 workbooks

[raffrobb]

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

 

[JLGWhiz]

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]

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.

 

[marcus]

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
Before:=Workbooks(Wrkbook).Sheets(1)
wbResults.Activate
WbCnt = WbCnt + 1
Next lCount
End If
End With
End With
End Sub

 

[Ron de Bruin]

See also
http://www.rondebruin.nl/copy4.htm

See the last example