exclude worksheet from multiworksheets being copied

irn

New Member
Joined
Aug 7, 2007
Messages
20
Currently I have a form on a workbook that has a command that runs vb that will copy all the contents from all the worksheets and copy/paste into a worksheet titled "summary". I need to add to that vb to exclude a worksheet labeled "users". I also need to check that duplicate rows do not get entered into the "summary" page when copied.

Code:
Private Sub cmdSummary_Click()

Dim LastRow As Long, r As Long
Sheets("Summary").Activate

LastRow = ActiveSheet.UsedRange.Rows.Count

For Each Sheet In Sheets

If Sheet.Index <> 1 Then

RowCount = Sheet.UsedRange.Rows.Count

Sheet.UsedRange.Copy Destination:=Sheets("Summary").Cells(LastRow + 1, 1)

LastRow = LastRow + RowCount

Sheet.UsedRange.Clear

End If
LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False

For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete

Next r

Application.ScreenUpdating = True

Next Sheet

End Sub

How could I add this?

Thanks!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
irn,

Please TEST this in COPY of you workbook FIRST.

Code:
Option Explicit
Private Sub cmdSummary_Click()
    Dim LastRow As Long, r As Long
    Sheets("Summary").Activate
    LastRow = ActiveSheet.UsedRange.Rows.Count
    Application.ScreenUpdating = False
    For Each Sheet In Sheets
        If Sheet.Index <> 1 And Sheet.Name <> "users" And Sheet.Name <> "Summary" Then
            RowCount = Sheet.UsedRange.Rows.Count
            Sheet.UsedRange.Copy Destination:=Sheets("Summary").Cells(LastRow + 1, 1)
            LastRow = LastRow + RowCount
            Sheet.UsedRange.Clear
        End If
        LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
        For r = LastRow To 1 Step -1
            If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
        Next r
    Next Sheet
    Application.ScreenUpdating = True
End Sub

Please TEST this in COPY of you workbook FIRST.

Have a great day,
Stan
 
Upvote 0
irn,

Please try the new code. You may have to order your sheets, left to right, starting with 'Summary', 'users', then the rest.

Please TEST this in COPY of you workbook FIRST.

Code:
Private Sub cmdSummary_Click()
    Dim LastRow As Long, r As Long
    Sheets("Summary").Activate
    LastRow = ActiveSheet.UsedRange.Rows.Count
    Application.ScreenUpdating = False
    For Each Sheet In Sheets
        If Sheet.Name = "users" Or Sheet.Name = "Summary" Then
            'Do nothing
        Else
            If Sheet.Index <> 1 Then
                RowCount = Sheet.UsedRange.Rows.Count
                Sheet.UsedRange.Copy Destination:=Sheets("Summary").Cells(LastRow + 1, 1)
                LastRow = LastRow + RowCount
                'Sheet.UsedRange.Clear
            End If
            LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
            For r = LastRow To 1 Step -1
                If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
            Next r
        End If
    Next Sheet
    Application.ScreenUpdating = True
End Sub


Please TEST this in COPY of you workbook FIRST.

Have a great day,
Stan
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top