Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheets (2003)
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. -- Thanks, Mike |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheets (2003)
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) " wrote in message ... 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. -- Thanks, Mike |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheets (2003)
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 InsertModule Copy the code above and paste into white pane that appears. Alt+F11 to return to Excel Alt+F8 to bring up the macro dialogueshighlight CopydataRun -- Regards Roger Govier " wrote in message ... 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. -- Thanks, Mike |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheets (2003)
Yes, all the layout is the same.
-- Mike "Bob Phillips" wrote: 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) " wrote in message ... 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. -- Thanks, Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I combine two worksheets in EXCEL 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 Worksheets | Excel Discussion (Misc queries) | |||
saving worksheets - Office 2003 | Excel Discussion (Misc queries) | |||
Many worksheets; Same formatting - Excel 2003 | Excel Discussion (Misc queries) |