Excel/excel 2010 macro question
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
If you don't already have S2-S60 listed in sheet S1 your workbook then you can do this
in sheet S1 assume
. . .
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.
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
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
Application.DisplayAlerts = False
Application.DisplayAlerts = True
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