INSERT IF STATEMENT AND RENAME WORKSHEET 2

[EVE734]

Hi - I have very little knowledge of VB coding, but am trying to set up what I believe is fairly simple. This is the code I have so far:

Sheets("exc34").Select
Range("A9").Select
Range("A9:G35").Select
Selection.Copy
Sheets("data").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Format").Select
Application.CutCopyMode = False
Sheets("Format").Copy After:=Sheets(3)
Sheets("Format (2)").Name

I want to insert an "IF" statement on the 2nd/3rd line that says if cell A9 is null, to stop the macro, but if it has a value to proceed with the next set of commands.

The second thing I want to do is with the last line of code - I want to rename a worksheet with the value of a specific cell. How do I format that in code?

THANK YOU IN ADVANCE!!!

Evelyn

 

[PHV]

With Sheets("exc34")
If Trim(.Range("A9").Value) = "" Then Exit Sub
.Range("A9:G35").Copy
End With
Sheets("data").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Format").Copy After:=Sheets(3)
Sheets("Format (2)").Name = Sheets("specific sheet").Range("specific range")


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[EVE734]

PH,

Thanks so much - this works perfectly!!!! Now, of course, I'd like to push my luck and just ask one more question!!!
For the line:

Sheets("Format").Copy After:=Sheets(3)

I am going to have many sheets created, and I would like each new sheet to go to the "end" of the file. The end sheet will be different each time a new sheet is created. Is there code to do this?

Last question, really!!

 

[SkipVought]

Sheets("Format").Copy After:=Sheets(Sheets("Format").count)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EVE734]

Thanks Skip, I copied the code exactly as you had written and I'm getting a run time error (438), "object doesn't support this property or method." What did I do wrong?

 

[SkipVought]

sorry...

Sheets("Format").Copy After:=Sheets(Sheets.count)

Runs to completion.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EVE734]

WOO HOO!!! THANK YOU SO MUCH!!! You Tek Tip guys are the best!!!!

Much appreciated,
Evelyn