ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Visual Basic - Variable Sheet Names (https://www.excelbanter.com/excel-discussion-misc-queries/170083-visual-basic-variable-sheet-names.html)

MarkT
Visual Basic - Variable Sheet Names
 
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

Don Guillett
Visual Basic - Variable Sheet Names
 
ALWAYS post your macro for comments

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MarkT" wrote in message
...
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


Bob Phillips
Visual Basic - Variable Sheet Names
 
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)



"MarkT" wrote in message
...
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



Dave Peterson
Visual Basic - Variable Sheet Names
 
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


MarkT wrote:

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

--

Dave Peterson

MarkT
Visual Basic - Variable Sheet Names
 
Sorry Don; here is my macro:

Sheets("Sheet1").Select
Sheets("Sheet1").Copy Befo=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

"Don Guillett" wrote:

ALWAYS post your macro for comments

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MarkT" wrote in message
...
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



MarkT
Visual Basic - Variable Sheet Names
 
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 Befo=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


"Bob Phillips" wrote:

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)



"MarkT" wrote in message
...
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




MarkT
Visual Basic - Variable Sheet Names
 
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 Befo=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


"Dave Peterson" wrote:

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


MarkT wrote:

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

--

Dave Peterson

Dave Peterson
Visual Basic - Variable Sheet Names
 
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





MarkT wrote:

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 Befo=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

"Dave Peterson" wrote:

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


MarkT wrote:

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

--

Dave Peterson


--

Dave Peterson

MarkT
Visual Basic - Variable Sheet Names
 
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

"Dave Peterson" wrote:

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





MarkT wrote:

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 Befo=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

"Dave Peterson" wrote:

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


MarkT wrote:

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

--

Dave Peterson


--

Dave Peterson

Dave Peterson
Visual Basic - Variable Sheet Names
 
Sorry. You have to change this line, too:

Sheets("Sheet1").Copy Befo=Sheets(1)
to
Activesheet.Copy Befo=Sheets(1)

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



MarkT wrote:

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

"Dave Peterson" wrote:

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





MarkT wrote:

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 Befo=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

"Dave Peterson" wrote:

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


MarkT wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

MarkT
Visual Basic - Variable Sheet Names
 
Dave, that works great; thank you very much!

"Dave Peterson" wrote:

Sorry. You have to change this line, too:

Sheets("Sheet1").Copy Befo=Sheets(1)
to
Activesheet.Copy Befo=Sheets(1)

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



MarkT wrote:

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

"Dave Peterson" wrote:

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





MarkT wrote:

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 Befo=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

"Dave Peterson" wrote:

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


MarkT wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 06:59 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com