We help IT Professionals succeed at work.

How do I consolidate specific worksheets, and get around the error if the worksheet does not exist?

tiehaze
tiehaze asked
on
Medium Priority
336 Views
Last Modified: 2010-04-16
Example: I have worksheets A, B, and C.  I want to consolidate these three sheets into my "Backup" WS. The problem I am having is that before this macro is run, WS "A", "B", or "C" my be deleted if nothing is in it.  How do I get around the error that pops up if, for example WS "A", "B", or "C" does not exist. Here is what I have so far:

Sheets("Backup").Select
Cells.ClearContents
r = 0
n = 0

For i = 1 To 3
        Select Case i
        Case 1
        WS_Name = "A"
        Case 2
        WS_Name = "B"
        Case 3
        WS_Name = "C"

        Sheets(i).Select
        GoSub DoCopy
        GoSub DoPaste
        n = n + r
        Next i
     
DoCopy:
    Cells(1, 1).CurrentRegion.Select
    Selection.Copy
    r = Selection.Rows.Count
    Return
     
DoPaste:
    Sheets("Backup").Select
    Cells(1, 1).Offset(n, 0).Select
    ActiveSheet.Paste
    Return

    End Sub
Comment
Watch Question

Put the macro in a module (not a sheet) where it is accessible to all worksheets.

Author

Commented:
Can you please explain? I thought all macros had to be in modules, which is how I am doing it now, and i am still getting the error.
Oppssss. Wrong answer. I though that you had the code in a worksheet.
Try putting this code in the workbook or a module...

Public Sub main()
On Error GoTo ErrorMsg

    Dim r As Long, n As Long, i As Integer
    Dim sh As Worksheet

    ' Clear Backup Sheet
    Sheets("Backup").Cells.ClearContents
   
    r = 0
    n = 0

    ' Iterate through all sheets, except Backup sheet
    For Each sh In ThisWorkbook.Sheets
        If LCase$(sh.Name) <> "backup" Then
            ' Copy sheet Contents - or selection (however this is not full proof!)
            sh.Select
            Cells(1, 1).CurrentRegion.Select
            Selection.Copy
            r = Selection.Rows.Count
            ' Paste into Back sheet previous copied selection
            Sheets("Backup").Select
            Cells(1, 1).Offset(n, 0).Select
            Sheets("Backup").Paste
            n = n + r
        End If
    Next sh

    Exit Sub


ErrorMsg:
    MsgBox Error, vbCritical

End Sub

Author

Commented:
I apologize, but I didn't note that there are some worksheets that I would not like to add. For example, if there were sheets A, B, C, D, and E and I would like to add all of the except D.  

I have previously been using worksheet.count in my formula. The macro would work much better if I could just state which worksheets specifically to add.  Thanks!
Try changing:

 If LCase$(sh.Name) <> "backup" Then

to (specify which sheets to copy)

  If LCase$(sh.Name) = "a" or LCase$(sh.Name) = "b" or LCase$(sh.Name) = "c" Then


that should do the trick.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.