Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit code - exclude sheets
Hello. I have the below piece of code that consolidates all visible sheets
with the exception of a few. Unfortunately, the number of sheets I need excluded keeps growing, and I have keep editing the code. Is there a way to have the code reference a worksheet (say called "Exclude"), and exclude the sheets named in a certain range, rather than editing this line of code: If ws.Name < DestSh.Name And ws.Name < "Total Signal" And ws.Name < "Upload" Then Thank you! Sub Consolidate() Dim ws As Worksheet Dim DestSh As Worksheet Dim shLast As Long Dim Last As Long Set DestSh = Worksheets("Upload") For Each ws In Worksheets If ws.Name < DestSh.Name And ws.Name < "Total Signal" And ws.Name < "Upload" Then Last = LastRow(DestSh) shLast = LastRow(ws) ws.Range(ws.Rows(1), ws.Rows(shLast)).Copy 'DestSh.Cells(Last + 1, 1) DestSh.Cells(Last + 2, 1).PasteSpecial Paste:=xlPasteValues End If Next End Sub Function LastRow(ws As Worksheet) On Error Resume Next LastRow = ws.Cells.Find(What:="*", _ After:=ws.Range("A1"), _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exclude HTML code | Excel Discussion (Misc queries) | |||
I wish to edit the calendar used in excel to exclude weekends | Excel Discussion (Misc queries) | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
Edit MACRO for all SHEETS | Excel Programming | |||
Emailing Sheets Code Edit | Excel Programming |