Visual Basic - Variable Sheet Names

  • Thread starter Thread starter MarkT
  • Start date Start date
M

MarkT

Hi Everyone,

I have a macro that I run on a 2007 workbook that makes a duplicate copy of
the current sheet by hitting a button with the macro associated with it. The
macro runs fine when the sheet is named "Sheet1"; however, once a duplicate
sheet has been created, I need to change the name of that sheet, then when I
go and hit my macro button again, I get an error since I have now renamed
"Sheet1" to something else.

In VB, is there a term that I may use to duplicate the current sheet
selected? I need to have a variable term used instead of the current
"sheet1" that is in my macro.

Thanks for your help as always!

Mark
 
Use the sheet codename.

If you look at the project window with a worksheet selected, you will see
there are two names, like Sheet1(Sheet1). the first is the codename, the
second is the Excel name. The codename remains even if the Excel name is
changed.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
If the code is under the worksheet (not in a general module, not behind the
ThisWorkbook module), then you could use the Me keyword to refer to the
worksheet owning the code.

If the code is in a General module, but the worksheet is the active worksheet,
you could use:

with activesheet
.range("a1").value = .name
end with
 
Sorry Don; here is my macro:

Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(1)
Range("E2:G2").Select
Selection.ClearContents
Range("K2").Select
Selection.ClearContents
Range("E7").Select
Selection.ClearContents
Range("I7").Select
Selection.ClearContents
Range("M7").Select
Selection.ClearContents
Range("E9").Select
Selection.ClearContents
Range("I9").Select
Selection.ClearContents
Range("M9").Select
Selection.ClearContents
Range("G12").Select
Selection.ClearContents
Range("M12").Select
Selection.ClearContents
Range("E20:F20").Select
Selection.ClearContents
Range("I20").Select
Selection.ClearContents
Range("G22:M22").Select
Selection.ClearContents
Range("D24:M24").Select
Selection.ClearContents
Range("S7").Select
Selection.ClearContents
Range("W7:X7").Select
Selection.ClearContents
Range("S9:AA9").Select
Selection.ClearContents
Range("Q11:AA12").Select
Selection.ClearContents
Range("S14").Select
Selection.ClearContents
Range("Y14").Select
Selection.ClearContents
Range("Q17:AA17").Select
Selection.ClearContents
Range("Q18:AA19").Select
Selection.ClearContents
Range("E2").Select
End Sub
 
Hi Bob, I don't see the sheet codename; I may not be looking at the macro as
you have descibed. Here is the macro:

Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(1)
Range("E2:G2").Select
Selection.ClearContents
Range("K2").Select
Selection.ClearContents
Range("E7").Select
Selection.ClearContents
Range("I7").Select
Selection.ClearContents
Range("M7").Select
Selection.ClearContents
Range("E9").Select
Selection.ClearContents
Range("I9").Select
Selection.ClearContents
Range("M9").Select
Selection.ClearContents
Range("G12").Select
Selection.ClearContents
Range("M12").Select
Selection.ClearContents
Range("E20:F20").Select
Selection.ClearContents
Range("I20").Select
Selection.ClearContents
Range("G22:M22").Select
Selection.ClearContents
Range("D24:M24").Select
Selection.ClearContents
Range("S7").Select
Selection.ClearContents
Range("W7:X7").Select
Selection.ClearContents
Range("S9:AA9").Select
Selection.ClearContents
Range("Q11:AA12").Select
Selection.ClearContents
Range("S14").Select
Selection.ClearContents
Range("Y14").Select
Selection.ClearContents
Range("Q17:AA17").Select
Selection.ClearContents
Range("Q18:AA19").Select
Selection.ClearContents
Range("E2").Select
End Sub
 
Hi Dave,

The macro is in the general module. Where would I insert your code, right
before the "Sheets("Sheet1") .Select line?

Here is the macro as I failed to submit it originally.

Thanks for your help!

Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(1)
Range("E2:G2").Select
Selection.ClearContents
Range("K2").Select
Selection.ClearContents
Range("E7").Select
Selection.ClearContents
Range("I7").Select
Selection.ClearContents
Range("M7").Select
Selection.ClearContents
Range("E9").Select
Selection.ClearContents
Range("I9").Select
Selection.ClearContents
Range("M9").Select
Selection.ClearContents
Range("G12").Select
Selection.ClearContents
Range("M12").Select
Selection.ClearContents
Range("E20:F20").Select
Selection.ClearContents
Range("I20").Select
Selection.ClearContents
Range("G22:M22").Select
Selection.ClearContents
Range("D24:M24").Select
Selection.ClearContents
Range("S7").Select
Selection.ClearContents
Range("W7:X7").Select
Selection.ClearContents
Range("S9:AA9").Select
Selection.ClearContents
Range("Q11:AA12").Select
Selection.ClearContents
Range("S14").Select
Selection.ClearContents
Range("Y14").Select
Selection.ClearContents
Range("Q17:AA17").Select
Selection.ClearContents
Range("Q18:AA19").Select
Selection.ClearContents
Range("E2").Select
End Sub
 
If you want to run the code to copy the activesheet, you would delete the
"Sheets("sheet1").select" line.

Then all the ranges that have the contents cleared will refer to the newly
created sheet.

ps.
range("E2:g2").select
selection.clearcontents

can be replaced with:
range("e2:g2").clearcontents

And you can do this kind of thing, too:

range("e2:g2,k2,i7,m7,e9,g12").clearcontents
 
Dave,

I deleted the line as you suggested, but it still stops at the next line of
code where again it has "sheet1" in the code.

I need to select the active sheet first, and copy it, then clear the various
cells in the new sheet, leaving the old sheet in the workbook as it was.

Is there any "Select active sheet" command available?

The macro works if the current sheet is named Sheet1; if I could just
replace the "sheet1" in the macro with whatever the current name of the sheet
was I should be good to go.

Thanks,

Mark
 
Sorry. You have to change this line, too:

Sheets("Sheet1").Copy Before:=Sheets(1)
to
Activesheet.Copy Before:=Sheets(1)

And you have to manually select that sheet to be copied first--if you're going
to rely on the activesheet code.


Dave,

I deleted the line as you suggested, but it still stops at the next line of
code where again it has "sheet1" in the code.

I need to select the active sheet first, and copy it, then clear the various
cells in the new sheet, leaving the old sheet in the workbook as it was.

Is there any "Select active sheet" command available?

The macro works if the current sheet is named Sheet1; if I could just
replace the "sheet1" in the macro with whatever the current name of the sheet
was I should be good to go.

Thanks,

Mark
Click to expand...
 
Dave, that works great; thank you very much!

Dave Peterson said:
Sorry. You have to change this line, too:

Sheets("Sheet1").Copy Before:=Sheets(1)
to
Activesheet.Copy Before:=Sheets(1)

And you have to manually select that sheet to be copied first--if you're going
to rely on the activesheet code.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copying Form cells (WS1) to Database (WS2) 1
Excel Macro to copy a row to another sheet based on date 0
EXCEL - IF(IFERROR(VLOOKUP question 0
Excel 2007: Help creating a macro that will run Solver? 3
Saving a worksheet 2
Sheet tab names change automatically 3
new to visual basic...help appreciated 2
Copy sheet 2

Back
Top