Re: Move worksheet to new book

Giganews Newsgroups
Subject: Re: Move worksheet to new book
Posted by:  Dave Peterson (ec357…
Date: Tue, 07 Oct 2003

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
                    Application.DisplayAlerts = True
                End If
                wks.Move  'to new workbook
            End If
        End With
    Next iCtr
End Sub

Brian wrote:
> 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.


Dave Peterson



In response to

Move worksheet to new book posted by Brian on Tue, 7 Oct 2003