Worksheets (2003)

[Jessica]

Hello,

I have 50+ worksheets with different names and data that needs to be
converted into one worksheet, so that I can have one data source when I merge
the data in publisher. However, I need some assistance with how and were to
place the 50+ worksheet names in the excel spreadsheet. I have include a
sample fields in the worksheets.

Fname, LName, Address etc.

the 1st worksheet name is Region1, then the 2nd is Region 2 etc.

 

[Bob Phillips]

Do all the worksheets have the same layout, so it is just a matter of
appending it all?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Roger Govier]

Hi Mike

Assuming all your sheets have the same number of columns (unspecified by
you), then create a sheet called Summary.
Set the column headings the same as your other sheets.
The following code assumes that Column J is where you want the sheet name to
appear (i.e. you data data in columns A through I)
Change the value for col to whatever is appropriate for you.

Sub copydata()
Dim wsd As Worksheet, ws As Worksheet
Dim lrd As Long, lrs As Long, i As Long
Dim source As Range, dest As Range, col As String

Application.ScreenUpdating = False
' change to suit which column you want sheetname to appear
col = "J" '<====

Set wsd = ThisWorkbook.Sheets("Summary")

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Summary" Then
ws.Activate
lrs = ws.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lrs
ws.Cells(i, "J") = ws.Name
Next i
Set source = ws.Range(Cells(2, "A"), Cells(lrs, col))
lrd = wsd.Cells(Rows.Count, "A").End(xlUp).Row + 1
wsd.Activate
Set dest = wsd.Range(Cells(lrd, "A"), Cells(lrd, col))
source.Copy dest
End If

Next
Sheets("Summary").Activate

Application.ScreenUpdating = True

End Sub

To use
Press F11 to invoke the VB Editor
Insert>Module
Copy the code above and paste into white pane that appears.
Alt+F11 to return to Excel
Alt+F8 to bring up the macro dialogues>highlight Copydata>Run

 

[Jessica]

Yes, all the layout is the same.

--
Mike

Do all the worksheets have the same layout, so it is just a matter of
appending it all?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)