Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a sheet and rename it
I am trying to create a macro that would copy a sheet within the same
workbook and rename it with the value from a cell on the sheet it copied it from. Please help... Thank you... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a sheet and rename it
Hi
Try this: Sub Macro1() Dim newSh As Worksheet Dim orgSh As Worksheet Set orgSh = Worksheets("Sheet1") Set newSh = Sheets("Sheet1").Copy(After:=Sheets(Sheets.Count)) newSh.Name = orgSh.Range("A1").Value End Sub Regards, Per "Dorian C. Chalom" skrev i meddelelsen ... I am trying to create a macro that would copy a sheet within the same workbook and rename it with the value from a cell on the sheet it copied it from. Please help... Thank you... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a sheet and rename it
Hi Per;
Finally got back to this and I got it work except for one issue... How do I determine the ame of the new sheet I add. I cannot always be certain it will be a certain name. Here is the Macro I ended up using. I can not copy the whole spreadsheet because it gave me an error so I had to do a range. If you can clean this up at all please feel free... Thank you. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 10/24/2009 by D Chalom ' ' Sheets("100634").Select Sheets.Add after:=Sheets(Sheets.Count) Sheets("Quote Form").Select Range("A1").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy Sheets("Sheet3").Select ActiveSheet.Paste Sheets("Sheet3").Select Sheets("Sheet3").Name = Worksheets("Quote Form").Range("H10").Value End Sub "Per Jessen" wrote in message ... Hi Try this: Sub Macro1() Dim newSh As Worksheet Dim orgSh As Worksheet Set orgSh = Worksheets("Sheet1") Set newSh = Sheets("Sheet1").Copy(After:=Sheets(Sheets.Count)) newSh.Name = orgSh.Range("A1").Value End Sub Regards, Per "Dorian C. Chalom" skrev i meddelelsen ... I am trying to create a macro that would copy a sheet within the same workbook and rename it with the value from a cell on the sheet it copied it from. Please help... Thank you... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a sheet and rename it
Don;
OK Close....I forgot to mention one thing...They want to Paste Values because they want to keep the copy for hostorical purposes. If I must figure this part on my own I will... Thank you... "Don Guillett" wrote in message ... One way without any SELECTIONS. Sub copyrangetonewsheetandname() With Sheets("Quote Form") newname = .Range("h10") .Range(.Range("a1"), .Range("a1").SpecialCells(xlLastCell)).Copy End With Sheets.Add after:=Sheets(Sheets.Count) With ActiveSheet .Paste .Name = newname .Range("a1").Select End With Application.CutCopyMode = False End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Dorian C. Chalom" wrote in message ... Hi Per; Finally got back to this and I got it work except for one issue... How do I determine the ame of the new sheet I add. I cannot always be certain it will be a certain name. Here is the Macro I ended up using. I can not copy the whole spreadsheet because it gave me an error so I had to do a range. If you can clean this up at all please feel free... Thank you. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 10/24/2009 by D Chalom ' ' Sheets("100634").Select Sheets.Add after:=Sheets(Sheets.Count) Sheets("Quote Form").Select Range("A1").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy Sheets("Sheet3").Select ActiveSheet.Paste Sheets("Sheet3").Select Sheets("Sheet3").Name = Worksheets("Quote Form").Range("H10").Value End Sub "Per Jessen" wrote in message ... Hi Try this: Sub Macro1() Dim newSh As Worksheet Dim orgSh As Worksheet Set orgSh = Worksheets("Sheet1") Set newSh = Sheets("Sheet1").Copy(After:=Sheets(Sheets.Count)) newSh.Name = orgSh.Range("A1").Value End Sub Regards, Per "Dorian C. Chalom" skrev i meddelelsen ... I am trying to create a macro that would copy a sheet within the same workbook and rename it with the value from a cell on the sheet it copied it from. Please help... Thank you... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a sheet and rename it
Should do it
Option Explicit Sub copyrangetonewsheetandnameValues() Dim source As Worksheet Dim la As String Set source = Sheets("Quote Form") Sheets.Add After:=Sheets(Sheets.Count) With ActiveSheet la = source.Cells.Find(What:="*", After:=[A1], _ SearchDirection:=xlPrevious).Address 'MsgBox la .Range("a1:" & la).Value = source.Range("a1:" & la).Value .Name = source.Range("h10") End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... One way without any SELECTIONS. Sub copyrangetonewsheetandname() With Sheets("Quote Form") newname = .Range("h10") .Range(.Range("a1"), .Range("a1").SpecialCells(xlLastCell)).Copy End With Sheets.Add after:=Sheets(Sheets.Count) With ActiveSheet .Paste .Name = newname .Range("a1").Select End With Application.CutCopyMode = False End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Dorian C. Chalom" wrote in message ... Hi Per; Finally got back to this and I got it work except for one issue... How do I determine the ame of the new sheet I add. I cannot always be certain it will be a certain name. Here is the Macro I ended up using. I can not copy the whole spreadsheet because it gave me an error so I had to do a range. If you can clean this up at all please feel free... Thank you. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 10/24/2009 by D Chalom ' ' Sheets("100634").Select Sheets.Add after:=Sheets(Sheets.Count) Sheets("Quote Form").Select Range("A1").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy Sheets("Sheet3").Select ActiveSheet.Paste Sheets("Sheet3").Select Sheets("Sheet3").Name = Worksheets("Quote Form").Range("H10").Value End Sub "Per Jessen" wrote in message ... Hi Try this: Sub Macro1() Dim newSh As Worksheet Dim orgSh As Worksheet Set orgSh = Worksheets("Sheet1") Set newSh = Sheets("Sheet1").Copy(After:=Sheets(Sheets.Count)) newSh.Name = orgSh.Range("A1").Value End Sub Regards, Per "Dorian C. Chalom" skrev i meddelelsen ... I am trying to create a macro that would copy a sheet within the same workbook and rename it with the value from a cell on the sheet it copied it from. Please help... Thank you... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a sheet and rename it
Don;
Sorry... Can I have the formatting also on the copy? Thank you... "Don Guillett" wrote in message ... Should do it Option Explicit Sub copyrangetonewsheetandnameValues() Dim source As Worksheet Dim la As String Set source = Sheets("Quote Form") Sheets.Add After:=Sheets(Sheets.Count) With ActiveSheet la = source.Cells.Find(What:="*", After:=[A1], _ SearchDirection:=xlPrevious).Address 'MsgBox la .Range("a1:" & la).Value = source.Range("a1:" & la).Value .Name = source.Range("h10") End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... One way without any SELECTIONS. Sub copyrangetonewsheetandname() With Sheets("Quote Form") newname = .Range("h10") .Range(.Range("a1"), .Range("a1").SpecialCells(xlLastCell)).Copy End With Sheets.Add after:=Sheets(Sheets.Count) With ActiveSheet .Paste .Name = newname .Range("a1").Select End With Application.CutCopyMode = False End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Dorian C. Chalom" wrote in message ... Hi Per; Finally got back to this and I got it work except for one issue... How do I determine the ame of the new sheet I add. I cannot always be certain it will be a certain name. Here is the Macro I ended up using. I can not copy the whole spreadsheet because it gave me an error so I had to do a range. If you can clean this up at all please feel free... Thank you. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 10/24/2009 by D Chalom ' ' Sheets("100634").Select Sheets.Add after:=Sheets(Sheets.Count) Sheets("Quote Form").Select Range("A1").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy Sheets("Sheet3").Select ActiveSheet.Paste Sheets("Sheet3").Select Sheets("Sheet3").Name = Worksheets("Quote Form").Range("H10").Value End Sub "Per Jessen" wrote in message ... Hi Try this: Sub Macro1() Dim newSh As Worksheet Dim orgSh As Worksheet Set orgSh = Worksheets("Sheet1") Set newSh = Sheets("Sheet1").Copy(After:=Sheets(Sheets.Count)) newSh.Name = orgSh.Range("A1").Value End Sub Regards, Per "Dorian C. Chalom" skrev i meddelelsen ... I am trying to create a macro that would copy a sheet within the same workbook and rename it with the value from a cell on the sheet it copied it from. Please help... Thank you... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy sheet and rename tab | Excel Programming | |||
How to copy a sheet and rename it with the value of two cells from the source sheet? | Excel Programming | |||
Button to copy sheet, rename sheet sequencially. | Excel Programming | |||
Copy a sheet and rename it | Excel Programming | |||
copy Sheet and rename it! | Excel Programming |