0 votes
in VBA by Beginner (33 points)

Dear all,

I have a workbook with maybe 15 worksheets. I was hoping to create a macro that will copy paste values of selected sheets into a new workbook.

There is a complication though. As the listed sheets change depending on the recipient, I cannot hard coded the sheet names into the macro. Hence I was wondering if I could make the macro:
- check if a worksheet name existed in Column D of Sheet1
- If the sheet name is listed, then copy paste values of that sheet to Workbook B
- if not, ignore and go to the next sheet
- cycle through all worksheets in Workbook A and copy paste value of all other sheets that apply to Workbook B

Is this do-able?

I found a code that was supposed to copy/paste values:

Sub CopyPaste()
    Dim Sh As Worksheet
    For Each Sh In ThisWorkbook.Worksheets
        If Sh.Visible = True Then
            Sh.Activate
            Sh.Cells.Copy
            Sh.Range("A1").PasteSpecial Paste:=xlValues
            Sh.Range("A1").Select
        End If
    Next Sh
    Application.CutCopyMode = False
End Sub

I was thinking if having the list was not possible, at least this would make the values-only workbook and I would slowly go in and delete those not applicable, but even this I get an error (Run-time error '1004'). :(

Would appreciate any help into this.

1 Answer

+2 votes
by Skilled (419 points)
selected by
 
Best answer

Hi Thales,

One method you can use is a table on a separate worksheet. I liked your idea of a worksheet name lookup, so that's what the table allows you to do. For more flexibility, you can set a value of Yes or No below each recipient. Here is a screenshot:

 

The first column of the table lists all of the Sheet names. If you rename a tab, be sure to update the table.

Each column to the right of of the Sheet names is a recipient. This could be a single person or a group of people, it doesn't matter. The headings are just a reminder to you, so that you can set the row values to Yes or No as needed.

The yellow cell has Data Validation, which only allows you to select one of the recipients. In the image below, you can see how that is set up:

The green Copy button is assigned to the macro that does the selections. Now, here is the code:

Sub CopySheets()

Dim CurrentWorkbook As Workbook
Dim NewWorkbook As Workbook
Dim LookupTable As ListObject
Dim i As Long

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set CurrentWorkbook = ThisWorkbook
Workbooks.Add
Set NewWorkbook = ActiveWorkbook

CurrentWorkbook.Activate
Set LookupTable = ActiveSheet.ListObjects("Table1")
With LookupTable.ListColumns(Range("Recipients").Value)
    For i = 1 To .DataBodyRange.Rows.Count
        ' Uncomment to see what's going on
        'Debug.Print i, .DataBodyRange.Rows(i), "Sheet "; LookupTable.DataBodyRange(i, 1)
        If .DataBodyRange.Rows(i) = "Yes" Then
            Sheets(LookupTable.DataBodyRange(i, 1).Value).Copy After:=NewWorkbook.Sheets(NewWorkbook.Sheets.Count)
            Application.CutCopyMode = False
            CurrentWorkbook.Activate
        End If
    Next
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

If all goes well, you will have a new, unsaved workbook with just the sheets relevant to the chosen recipient. Depending on your Excel settings, you may have 1 or more blank sheets (the default is 3). You can delete them before saving your new workbook.

You will want to add error-handling to ensure that ScreenUpdating and DisplayAlerts are set to TRUE if something goes wrong.

Cheers,

Mitch

 

by Beginner (33 points)
+1
Hi Mitch,

You understood me exactly and the macro worked exactly!!! Sooo very very happy!!!

Thank you again!
by Skilled (419 points)
Hi Thales,

You are very welcome. I am happy to have helped.

Cheers,

Mitch

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...