Copy & Paste into New Worksheet Macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brad - 22 Jun 2005 19:28 GMT

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!

Reply to this Message

Jean-Claude Arbaut - 23 Jun 2005 00:28 GMT

> Hi all:
> I'm trying to create a macro for the following situation:
[quoted text clipped - 12 lines]
>
> 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 ?

Reply to this Message

JE McGimpsey - 23 Jun 2005 21:10 GMT

> Hi all:
> I'm trying to create a macro for the following situation:
[quoted text clipped - 10 lines]
> 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

Reply to this Message

Mac Forum / Applications / Excel / June 2005

Copy & Paste into New Worksheet Macro