ReDim Problem

[Casey]

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

 

[Trevor Shuttleworth]

Casey

easiest way is to repeat the line:

Lastsheet = Sheets.Count

after you've copied the sheet.

Lastsheet = Sheets.Count

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

Lastsheet = Sheets.Count

Then you don't have to recode any of the following statements. You've
actually increased the number of sheets so "LastSheet" no longer refers to
the last sheet but the one before that.

Regards

Trevor

 

[Ron de Bruin]

Try this one Casey

Sub test()
Sheets("CE(1)").Copy After:=Sheets(ThisWorkbook.Sheets.Count)
With ActiveSheet
.Range("M7:M8,F45:F46,A13:I31").ClearContents
.Range("M7").Select
End With
End Sub

 

[Casey]

I happened to try Ron solution first and it worked perfectly, but thanks
to both Ron and Trevor for the help guys. I'm pretty good at the heavy
formula stuff, but this VBA can take my work a lot farther and make it
tons easier, if I can get the hang of it. Thanks again, you guys are
brilliant.

 

[Trevor Shuttleworth]

Casey

I was just being lazy. Ron did a "proper job" ;-) It's neat and it'll
be quicker.

Regards

Trevor