Solved

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

Posted on 2006-10-25
5
316 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Introduction to Processes

624 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