I'm new to VBA. I could use your help. Below is my code. What is
supposed to happen, is a sheet named CE(1) is supposed to get copied
and placed at the end of all the sheets. Then the second part is
supposed to clear certain data from the new sheet, but instead it
clears it from the sheet before it.

Many thanks for any help you can give.


Private Sub CommandButton1_Click()
'
' Add_New_CE Macro
' Macro recorded 10/13/2002 by Casey Wilkins
'

'
Dim Lastsheet As Long

Lastsheet = Sheets.Count

Sheets("CE(1)").Select
Sheets("CE(1)").Copy After:=Sheets(Lastsheet)

'This bit of code is supposed to clear the old data from the new
sheet,
'however, it clears the data from the sheet before it. As an example
if
'the new sheet created above is the 8th sheet the following code
clears
'the data from the seventh sheet. I figure Lastsheet needs to re
dimention
'it shelf, just not sure how.

Sheets(Lastsheet).Select
Sheets(Lastsheet).Range("M7:M8").Select
Selection.ClearContents
Sheets(Lastsheet).Range("F45:F46").Select
Selection.ClearContents
Sheets(Lastsheet).Range("A13:I31").Select
Selection.ClearContents
'This takes the user back to the beginning of the input range
Sheets(Lastsheet).Range("M7").Select
End Sub


---
Message posted from http://www.ExcelForum.com/