Copying Worksheets From Different Workbooks using Marco

[Max2073]

I am hoping that someone can help me.

I need some help with copying worksheets. I extract data from an operating
system using Discoverer and then export into excel. These reports are ran
weekly. Discoverer automatically names the workbooks the same name each
week. There are five workbooks.

I would like to create a macro that copies the Worksheets(1) of each of the
five workbooks into one workbook (as separate sheets). I would like to
specific the files and files path in the code (not using a browse option).

 

[Eduardo]

Hi,
Look into Ron web

http://www.rondebruin.nl/copy3.htm

if this helps please click yes thanks

 

[Max2073]

Hi,

This appears to merge the worksheets into one, I want to copy the sheets
from different workbooks and create a new workbook with the sheets being
retained and I want to state paths and filenames eg: c:\documents and
settings\max\example1.xls (sheet1) and c:\documents and
settings\max\example2.xls (sheet1) etc

 

[Jacob Skaria]

Try the below

Sub savejobC()

Dim wb1 As Workbook, wb2 As Workbook, intTemp As Integer
Dim strFolder As String, arrBooks As Variant

strFolder = "c:\"
arrBooks = Array("1.xls", "2.xls", "3.xls", "4.xls", "5.xls")

Set wb1 = ActiveWorkbook 'current workbook
'Set wb1 = Workbooks.Open("<fullpath>\<filename.ext>") 'existing workbook

For intTemp = 0 To 4
Set wb2 = Workbooks.Open(CStr(strFolder & arrBooks(intTemp)))
wb2.Sheets(1).Copy After:=wb1.Sheets(wb1.Worksheets.Count)
wb2.Close False
Next

End Sub

If this post helps click Yes

 

[Ron de Bruin]

See
http://www.rondebruin.nl/fso.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm