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
Solved

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

Posted on 2006-10-25
5
313 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 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
groupSumClump challenge 9 125
firstswap challenge 20 82
Please explain: Aspect Oriented Programming 2 101
egit plugin on eclipse 8 82
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

829 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