Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2006-10-25
5
Medium Priority
?
319 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
0
Comment
Question by:tiehaze
  • 3
  • 2
5 Comments
 
LVL 2

Expert Comment

by:ruijclaro
ID: 17804864
Put the macro in a module (not a sheet) where it is accessible to all worksheets.
0
 

Author Comment

by:tiehaze
ID: 17804893
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
 
LVL 2

Expert Comment

by:ruijclaro
ID: 17804968
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
 

Author Comment

by:tiehaze
ID: 17805029
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
 
LVL 2

Accepted Solution

by:
ruijclaro earned 2000 total points
ID: 17805052
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Starting up a Project

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question