• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

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

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
0
tiehaze
Asked:
tiehaze
  • 3
  • 2
1 Solution
 
ruijclaroCommented:
Put the macro in a module (not a sheet) where it is accessible to all worksheets.
0
 
tiehazeAuthor 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.
0
 
ruijclaroCommented:
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
0
 
tiehazeAuthor 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!
0
 
ruijclaroCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now