Finding first blank worksheet
 

Jim,

Try the following code:

Sub AAA()

Dim WS As Worksheet
Dim ActiveWS As Worksheet
Dim FirstBlankSheet As Worksheet

Set ActiveWS = ActiveSheet
For Each WS In Worksheets
If Application.WorksheetFunction.CountA(WS.UsedRange. Cells) = 0 Then
Set FirstBlankSheet = WS
Exit For
End If
Next WS

If FirstBlankSheet Is Nothing Then
MsgBox "Cannot find a blank sheet"
Exit Sub
End If
ActiveWS.UsedRange.Copy
FirstBlankSheet.Range("A1").PasteSpecial xlPasteFormats
FirstBlankSheet.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False

End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Jim Tibbetts" wrote in message
...
I have a page in my workbook that contains formulas where the data changes
weekly. I want to copy the new data every week to the first blank sheet
and
then PasteSpecial the formats and values. I've come close using tips from
other posts, but I'm not quite there.
Thanks for any help.
--
Jim T

Finding first blank worksheet
 

That works great. Thanks for the quick reply. I'm sure you'll be hearing from
me again, as I am migrating over from many years with QPW.
--
Jim T


"Chip Pearson" wrote:

Jim,

Try the following code:

Sub AAA()

Dim WS As Worksheet
Dim ActiveWS As Worksheet
Dim FirstBlankSheet As Worksheet

Set ActiveWS = ActiveSheet
For Each WS In Worksheets
If Application.WorksheetFunction.CountA(WS.UsedRange. Cells) = 0 Then
Set FirstBlankSheet = WS
Exit For
End If
Next WS

If FirstBlankSheet Is Nothing Then
MsgBox "Cannot find a blank sheet"
Exit Sub
End If
ActiveWS.UsedRange.Copy
FirstBlankSheet.Range("A1").PasteSpecial xlPasteFormats
FirstBlankSheet.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False

End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Jim Tibbetts" wrote in message
...
I have a page in my workbook that contains formulas where the data changes
weekly. I want to copy the new data every week to the first blank sheet
and
then PasteSpecial the formats and values. I've come close using tips from
other posts, but I'm not quite there.
Thanks for any help.
--
Jim T