Macro to copy a worksheet and rename based upon text entered in 1

[edeaston]

Hi,

I am sure this has been asked elsewhere on this forum but I cant find the
answer that I need.

I have a template worksheet created in Excel which people can fill out to
specify an analysis.
My aim is to have a cell where the user enters a new worksheet name so that
when they click on a button it copies the worksheet and renames it to the
current name plus what they entered into the cell.
For example my template worksheet is called "ABC". If the user enters "1_EE"
into cell A2 and then activate the macro it creates a copy of the worksheet
called "ABC 1_EE".

Any ideas?

Also, soon I will be developing a workbook which will need to link to
multiple documents. These links will need to be updated every month and I am
planning on setting it up in the following way:

All formulas / links to the external source are set to month 1s names and
locations such as XXXXX, YYYY, ZZZZ etc
On a front page I plan to have a series of cells where the user enters the
current link and the new link:
Cell A1: Current Cell A2: XXXX
Cell A1: New Cell A3: 1111

I want to create a macro that does a Find and replace in the whole workbook
that looks for the data in A2 and replaces all instances with the data from
A3.

Any thoughts?

Thanks in advance for your help on either / both topics?

Ed

 

[Eduardo]

Hi,
first you need to get the worksheet name so let's say in cell A1 copy this
formula
=MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255)
now in A2 the user will enter 1_EE, in cell A3 enter
=A1&" "&A2
now you have the name of the worksheet you want to create

Sub Add_Sheets22()
Dim rCell As Range
For Each rCell In Range("A3")
With Worksheets.Add(After:=Worksheets(Worksheets.Count))
.Name = rCell.Value
End With
Next rCell
End Sub
Then select your sheet and copy it in the new worksheet created

 

[edeaston]

Hi Eduardo,

Thanks for your help. Is there any way of doing this so that I dont have to
copy the data onto the newly created sheet?

The users of this document will not necessarily be Excel-literate so I
wanted to make it as easy as possible for them.

Thanks

Ed

Hi,
first you need to get the worksheet name so let's say in cell A1 copy this
formula
=MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255)
now in A2 the user will enter 1_EE, in cell A3 enter
=A1&" "&A2
now you have the name of the worksheet you want to create

Sub Add_Sheets22()
Dim rCell As Range
For Each rCell In Range("A3")
With Worksheets.Add(After:=Worksheets(Worksheets.Count))
.Name = rCell.Value
End With
Next rCell
End Sub
Then select your sheet and copy it in the new worksheet created

Hi,

I am sure this has been asked elsewhere on this forum but I cant find the
answer that I need.

I have a template worksheet created in Excel which people can fill out to
specify an analysis.
My aim is to have a cell where the user enters a new worksheet name so that
when they click on a button it copies the worksheet and renames it to the
current name plus what they entered into the cell.
For example my template worksheet is called "ABC". If the user enters "1_EE"
into cell A2 and then activate the macro it creates a copy of the worksheet
called "ABC 1_EE".

Any ideas?

Also, soon I will be developing a workbook which will need to link to
multiple documents. These links will need to be updated every month and I am
planning on setting it up in the following way:

All formulas / links to the external source are set to month 1s names and
locations such as XXXXX, YYYY, ZZZZ etc
On a front page I plan to have a series of cells where the user enters the
current link and the new link:
Cell A1: Current Cell A2: XXXX
Cell A1: New Cell A3: 1111

I want to create a macro that does a Find and replace in the whole workbook
that looks for the data in A2 and replaces all instances with the data from
A3.

Any thoughts?

Thanks in advance for your help on either / both topics?

Ed

 

[Eduardo]

Hi Ed,
here is the code to copy your spreadsheet into the new one
Sheets("youroriginal sheet").Select
Cells.Select
Selection.Copy

workbooktab = Range("A3").Value
Worksheets(workbooktab).Select
Range("A3").Select
ActiveSheet.Paste
Application.CutCopyMode = False


End Sub

Hi Eduardo,

Thanks for your help. Is there any way of doing this so that I dont have to
copy the data onto the newly created sheet?

The users of this document will not necessarily be Excel-literate so I
wanted to make it as easy as possible for them.

Thanks

Ed

Hi,
first you need to get the worksheet name so let's say in cell A1 copy this
formula
=MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255)
now in A2 the user will enter 1_EE, in cell A3 enter
=A1&" "&A2
now you have the name of the worksheet you want to create

Sub Add_Sheets22()
Dim rCell As Range
For Each rCell In Range("A3")
With Worksheets.Add(After:=Worksheets(Worksheets.Count))
.Name = rCell.Value
End With
Next rCell
End Sub
Then select your sheet and copy it in the new worksheet created

Hi,

I am sure this has been asked elsewhere on this forum but I cant find the
answer that I need.

I have a template worksheet created in Excel which people can fill out to
specify an analysis.
My aim is to have a cell where the user enters a new worksheet name so that
when they click on a button it copies the worksheet and renames it to the
current name plus what they entered into the cell.
For example my template worksheet is called "ABC". If the user enters "1_EE"
into cell A2 and then activate the macro it creates a copy of the worksheet
called "ABC 1_EE".

Any ideas?

Also, soon I will be developing a workbook which will need to link to
multiple documents. These links will need to be updated every month and I am
planning on setting it up in the following way:

All formulas / links to the external source are set to month 1s names and
locations such as XXXXX, YYYY, ZZZZ etc
On a front page I plan to have a series of cells where the user enters the
current link and the new link:
Cell A1: Current Cell A2: XXXX
Cell A1: New Cell A3: 1111

I want to create a macro that does a Find and replace in the whole workbook
that looks for the data in A2 and replaces all instances with the data from
A3.

Any thoughts?

Thanks in advance for your help on either / both topics?

Ed

 

[edeaston]

Perfect - thanks, that solved it!

Hi Ed,
here is the code to copy your spreadsheet into the new one
Sheets("youroriginal sheet").Select
Cells.Select
Selection.Copy

workbooktab = Range("A3").Value
Worksheets(workbooktab).Select
Range("A3").Select
ActiveSheet.Paste
Application.CutCopyMode = False


End Sub

Hi Eduardo,

Thanks for your help. Is there any way of doing this so that I dont have to
copy the data onto the newly created sheet?

The users of this document will not necessarily be Excel-literate so I
wanted to make it as easy as possible for them.

Thanks

Ed

Hi,
first you need to get the worksheet name so let's say in cell A1 copy this
formula
=MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255)
now in A2 the user will enter 1_EE, in cell A3 enter
=A1&" "&A2
now you have the name of the worksheet you want to create

Sub Add_Sheets22()
Dim rCell As Range
For Each rCell In Range("A3")
With Worksheets.Add(After:=Worksheets(Worksheets.Count))
.Name = rCell.Value
End With
Next rCell
End Sub
Then select your sheet and copy it in the new worksheet created



:

Hi,

I am sure this has been asked elsewhere on this forum but I cant find the
answer that I need.

I have a template worksheet created in Excel which people can fill out to
specify an analysis.
My aim is to have a cell where the user enters a new worksheet name so that
when they click on a button it copies the worksheet and renames it to the
current name plus what they entered into the cell.
For example my template worksheet is called "ABC". If the user enters "1_EE"
into cell A2 and then activate the macro it creates a copy of the worksheet
called "ABC 1_EE".

Any ideas?

Also, soon I will be developing a workbook which will need to link to
multiple documents. These links will need to be updated every month and I am
planning on setting it up in the following way:

All formulas / links to the external source are set to month 1s names and
locations such as XXXXX, YYYY, ZZZZ etc
On a front page I plan to have a series of cells where the user enters the
current link and the new link:
Cell A1: Current Cell A2: XXXX
Cell A1: New Cell A3: 1111

I want to create a macro that does a Find and replace in the whole workbook
that looks for the data in A2 and replaces all instances with the data from
A3.

Any thoughts?

Thanks in advance for your help on either / both topics?

Ed

 

[Eduardo]

Your welcome, If this post helps you please say yes to the question. thank you

Perfect - thanks, that solved it!

Hi Ed,
here is the code to copy your spreadsheet into the new one
Sheets("youroriginal sheet").Select
Cells.Select
Selection.Copy

workbooktab = Range("A3").Value
Worksheets(workbooktab).Select
Range("A3").Select
ActiveSheet.Paste
Application.CutCopyMode = False


End Sub

Hi Eduardo,

Thanks for your help. Is there any way of doing this so that I dont have to
copy the data onto the newly created sheet?

The users of this document will not necessarily be Excel-literate so I
wanted to make it as easy as possible for them.

Thanks

Ed

:

Hi,
first you need to get the worksheet name so let's say in cell A1 copy this
formula
=MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255)
now in A2 the user will enter 1_EE, in cell A3 enter
=A1&" "&A2
now you have the name of the worksheet you want to create

Sub Add_Sheets22()
Dim rCell As Range
For Each rCell In Range("A3")
With Worksheets.Add(After:=Worksheets(Worksheets.Count))
.Name = rCell.Value
End With
Next rCell
End Sub
Then select your sheet and copy it in the new worksheet created



:

Hi,

I am sure this has been asked elsewhere on this forum but I cant find the
answer that I need.

I have a template worksheet created in Excel which people can fill out to
specify an analysis.
My aim is to have a cell where the user enters a new worksheet name so that
when they click on a button it copies the worksheet and renames it to the
current name plus what they entered into the cell.
For example my template worksheet is called "ABC". If the user enters "1_EE"
into cell A2 and then activate the macro it creates a copy of the worksheet
called "ABC 1_EE".

Any ideas?

Also, soon I will be developing a workbook which will need to link to
multiple documents. These links will need to be updated every month and I am
planning on setting it up in the following way:

All formulas / links to the external source are set to month 1s names and
locations such as XXXXX, YYYY, ZZZZ etc
On a front page I plan to have a series of cells where the user enters the
current link and the new link:
Cell A1: Current Cell A2: XXXX
Cell A1: New Cell A3: 1111

I want to create a macro that does a Find and replace in the whole workbook
that looks for the data in A2 and replaces all instances with the data from
A3.

Any thoughts?

Thanks in advance for your help on either / both topics?

Ed