Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help editing a macro
This macro was coded from here on this site (thanks for everyones help) and
it basically takes all the information from every worksheet in the workbook and compiles it into one long list of data. Sub UpdateMaster() Dim ws As Worksheet 'Utility worksheet variable Dim Dest As Range 'The cell in Col B of Master sht in which to paste If Range("A" & Rows.Count).End(xlUp).Row 1 Then Range("A2", Range("B" & Rows.Count).End(xlUp).Offset(2, -1)).Resize(, 13).ClearContents End If Set Dest = Range("B2") For Each ws In ActiveWorkbook.Worksheets If ws.Name < "Master" Then Dest.Offset(, -1).Value = ws.Name With ws .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 12).Copy Dest Set Dest = Range("B" & Rows.Count).End(xlUp).Offset(2) End With End If Next ws End Sub My question is, how do I get it to ignore the first worksheet in the workbook? Is there some command I can give to do this in the code? Any help is appretiated.. If this is too vague I'll try and make my question more specific |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help editing a macro
I'm sure there are other ways, but I would do it this way.
Sub UpdateMaster() Dim ws As Worksheet 'Utility worksheet variable Dim Dest As Range 'The cell in Col B of Master sht in which to paste If Range("A" & Rows.Count).End(xlUp).Row 1 Then Range("A2", Range("B" & Rows.Count).End(xlUp).Offset(2, -1)).Resize(, 13).ClearContents End If Set Dest = Range("B2") For sh = 2 To Worksheets.Count 'For Each ws In ActiveWorkbook.Worksheets If Sheets(sh).Name < "Master" Then Dest.Offset(, -1).Value = sheets(sh).Name With Sheets(sh) .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 12).Copy Dest Set Dest = Range("B" & Rows.Count).End(xlUp).Offset(2) End With End If Next sh End Sub Note: I've remarked statements with "ws" or changed the reference to "Sheets(sh)". HTH, Paul -- "ZBelden" wrote in message ... This macro was coded from here on this site (thanks for everyones help) and it basically takes all the information from every worksheet in the workbook and compiles it into one long list of data. Sub UpdateMaster() Dim ws As Worksheet 'Utility worksheet variable Dim Dest As Range 'The cell in Col B of Master sht in which to paste If Range("A" & Rows.Count).End(xlUp).Row 1 Then Range("A2", Range("B" & Rows.Count).End(xlUp).Offset(2, -1)).Resize(, 13).ClearContents End If Set Dest = Range("B2") For Each ws In ActiveWorkbook.Worksheets If ws.Name < "Master" Then Dest.Offset(, -1).Value = ws.Name With ws .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 12).Copy Dest Set Dest = Range("B" & Rows.Count).End(xlUp).Offset(2) End With End If Next ws End Sub My question is, how do I get it to ignore the first worksheet in the workbook? Is there some command I can give to do this in the code? Any help is appretiated.. If this is too vague I'll try and make my question more specific |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help editing a macro
or name the other sheet to exclude
If ws.Name < "Master" and ws.name < "Whatever" Then Mike "ZBelden" wrote: This macro was coded from here on this site (thanks for everyones help) and it basically takes all the information from every worksheet in the workbook and compiles it into one long list of data. Sub UpdateMaster() Dim ws As Worksheet 'Utility worksheet variable Dim Dest As Range 'The cell in Col B of Master sht in which to paste If Range("A" & Rows.Count).End(xlUp).Row 1 Then Range("A2", Range("B" & Rows.Count).End(xlUp).Offset(2, -1)).Resize(, 13).ClearContents End If Set Dest = Range("B2") For Each ws In ActiveWorkbook.Worksheets If ws.Name < "Master" Then Dest.Offset(, -1).Value = ws.Name With ws .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 12).Copy Dest Set Dest = Range("B" & Rows.Count).End(xlUp).Offset(2) End With End If Next ws End Sub My question is, how do I get it to ignore the first worksheet in the workbook? Is there some command I can give to do this in the code? Any help is appretiated.. If this is too vague I'll try and make my question more specific |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help editing a macro
Worked like a charm, thanks guys.
"PCLIVE" wrote: I'm sure there are other ways, but I would do it this way. Sub UpdateMaster() Dim ws As Worksheet 'Utility worksheet variable Dim Dest As Range 'The cell in Col B of Master sht in which to paste If Range("A" & Rows.Count).End(xlUp).Row 1 Then Range("A2", Range("B" & Rows.Count).End(xlUp).Offset(2, -1)).Resize(, 13).ClearContents End If Set Dest = Range("B2") For sh = 2 To Worksheets.Count 'For Each ws In ActiveWorkbook.Worksheets If Sheets(sh).Name < "Master" Then Dest.Offset(, -1).Value = sheets(sh).Name With Sheets(sh) .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 12).Copy Dest Set Dest = Range("B" & Rows.Count).End(xlUp).Offset(2) End With End If Next sh End Sub Note: I've remarked statements with "ws" or changed the reference to "Sheets(sh)". HTH, Paul -- "ZBelden" wrote in message ... This macro was coded from here on this site (thanks for everyones help) and it basically takes all the information from every worksheet in the workbook and compiles it into one long list of data. Sub UpdateMaster() Dim ws As Worksheet 'Utility worksheet variable Dim Dest As Range 'The cell in Col B of Master sht in which to paste If Range("A" & Rows.Count).End(xlUp).Row 1 Then Range("A2", Range("B" & Rows.Count).End(xlUp).Offset(2, -1)).Resize(, 13).ClearContents End If Set Dest = Range("B2") For Each ws In ActiveWorkbook.Worksheets If ws.Name < "Master" Then Dest.Offset(, -1).Value = ws.Name With ws .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 12).Copy Dest Set Dest = Range("B" & Rows.Count).End(xlUp).Offset(2) End With End If Next ws End Sub My question is, how do I get it to ignore the first worksheet in the workbook? Is there some command I can give to do this in the code? Any help is appretiated.. If this is too vague I'll try and make my question more specific |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro editing | Excel Discussion (Misc queries) | |||
Editing a macro | Excel Discussion (Misc queries) | |||
Editing a macro | Excel Discussion (Misc queries) | |||
Editing a Macro | Excel Discussion (Misc queries) | |||
Editing Macro | Excel Discussion (Misc queries) |