Copying Worksheets From Different Workbooks using Marco

M

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).
 
M

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top