Copy & Paste into New Worksheet Macro

[Brad]

Hi all:
I'm trying to create a macro for the following situation:

1) I have 10 spreadsheets open simultaneously (all separate files - each
with one sheet)

2) For one of the spreadsheets, I would like to copy all the other files and
insert them as separate worksheets within

So basically I'm trying to consolidate 10 spreadsheets into 1 consisting of
10 worksheets

I tried recording a macro but it looks for the literal name of the
spreadsheet file in the VB code.

Thanks for any help!

 

[Jean-Claude Arbaut]

Hi all:
I'm trying to create a macro for the following situation:

1) I have 10 spreadsheets open simultaneously (all separate files - each
with one sheet)

2) For one of the spreadsheets, I would like to copy all the other files and
insert them as separate worksheets within

So basically I'm trying to consolidate 10 spreadsheets into 1 consisting of
10 worksheets

I tried recording a macro but it looks for the literal name of the
spreadsheet file in the VB code.

Thanks for any help!

Could you provide the source of your macro ? Maybe the files are just
strings, so you'll be able to change them at runtime.

It would also be useful to know how you would like to change the file names:
are they generated by program, are they in a separate file or spreadsheet ?

 

[JE McGimpsey]

Hi all:
I'm trying to create a macro for the following situation:

1) I have 10 spreadsheets open simultaneously (all separate files - each
with one sheet)

2) For one of the spreadsheets, I would like to copy all the other files and
insert them as separate worksheets within

So basically I'm trying to consolidate 10 spreadsheets into 1 consisting of
10 worksheets

I tried recording a macro but it looks for the literal name of the
spreadsheet file in the VB code.

Here's one I've used before. It's overkill for your situation, because
it assumes that you have multiple worksheets in your workbooks, and it
only copies worksheets that contain data. But it should work for you,
too.

Public Sub CopyToOneBook()
Dim wb As Workbook
Dim ws As Worksheet

With Workbooks("Summary.xls")
For Each wb In Workbooks
If wb.Name <> .Name Then
For Each ws In wb.Worksheets
If Application.CountA(ws.Cells) > 0 Then _
ws.Copy After:=.Sheets(.Sheets.Count)
Next ws
End If
Next wb
End With
End Sub