You are here:

Excel/excel 2010 macro question

Advertisement


Question
Hello Sir,
I have a workbook (W1) with approx. sixty worksheets (S2-S60).
The 1st worksheet should contain the list of all 60 worksheets names and the possibility to mark some of them, the column next to the name (let's say by V or X).
I'm looking for an automation to copy only those marked worksheets with all their data in a new workbook called W2.
Any idea highly appreciated,

Thank you in advance, Sam

Answer
Sam

If you don't already have S2-S60 listed in sheet S1  your workbook then you can do this

in sheet S1 assume

A1:  ="S"&row()+1
A2:  ="S"&row()+1
. . .
A59: ="S"&row()+1

My code assumes your sheet names start in A1 and your marks would be next to them in column B.  I further assume that if any cell in column B next to the name in column A is not empty, then the sheet listed next to that cell should be copied.  

This worked for me given my assumptions.


Sub copysheets()
 Dim sh As Worksheet, cnt As Long, bk As Workbook
 Dim sh1 As Worksheet, cell As Range
 
 Set sh = ThisWorkbook.Worksheets("S1")
 cnt = Application.CountIf(sh.Range("B1:B59"), "<>")
 If cnt > 1 Then
   ' add a 1 sheet workbook
   Workbooks.Add Template:=xlWBATWorksheet
   Set bk = ActiveWorkbook
   For Each cell In sh.Range("B1:B59")
     If Len(Trim(cell.Value)) > 0 Then
       Set sh1 = Nothing
       On Error Resume Next
        Set sh1 = ThisWorkbook.Worksheets(cell.Offset(0, -1).Text)
       On Error GoTo 0
       If Not sh1 Is Nothing Then
         sh1.Copy After:=bk.Worksheets(bk.Worksheets.Count)
       End If
     End If
   Next
   Application.DisplayAlerts = False
   bk.Worksheets(1).Delete
   Application.DisplayAlerts = True
End If
End Sub

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.