Move worksheet to new book

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I have 14 worksheets within a workbook. In the middle are
worksheets named Sheet1, Sheet2....Sheet10 Is it possible
using a macro to see if there is anything written in cell
A1 on Sheet1, and if there is, then move it to a new book,
and then check Sheet2 and so on? And if there is no
information on the worksheet I would like to delete it.

TIA
 
Try this against a copy (since it destroys worksheets):

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim iCtr As Long
Dim wkbk As Workbook

Set wkbk = ActiveWorkbook

For iCtr = 1 To 10 'sheet1 through sheet10
Set wks = wkbk.Worksheets("sheet" & iCtr)
With wks
If .Range("A1").Value = "" Then
If Application.CountA(.UsedRange) = 0 Then
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End If
Else
wks.Move 'to new workbook
End If
End With
Next iCtr
End Sub
 
Back
Top