| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Sheet and rename it.
Hi Everyone
I've got this macro that made a backup of my invoice and renamed it, but now instead of making a backup copy, it renames my original Invoice copy. Sub Make_Bkup_Copy() On Error Resume Next ActiveSheet.Copy after:=lastSheet Set ws = ActiveSheet ws.Name = Range("K2") & "-" & Range("G1") With Target If .Value < "" Then Name = .Value End If End With Sheets("Summary").Columns("A:F").fmTextAlign , 2 Sheets("Summary").Move befo=Sheets("Facture") Sheets("Facture").Select Range("K2").Select End Sub It should copy my Invoice , rename the Tab with cell K2 and G1 ( Invoice number with name) Any help would be appreciated. Thank you Cimjet |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Sheet and rename it.
If this code ever worked as you say, then you must have made a change to the
code at some point. You have not set lastSheet Sub Make_Bkup_Copy() Dim lastSheet As Worksheet Dim ws as Worksheet Set lastSheet = Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Copy after:=lastSheet Set ws = ActiveSheet ws.Name = Range("K2") & "-" & Range("G1") 'what is this part designed to do? ' With Target ' If .Value < "" Then ' Name = .Value ' End If ' End With Sheets("Summary").Columns("A:F").fmTextAlign , 2 Sheets("Summary").Move befo=Sheets("Facture") Sheets("Facture").Select Range("K2").Select End Sub Gord Dibben MS Excel MVP On Tue, 7 Jun 2011 08:36:46 -0400, "Cimjet" wrote: Hi Everyone I've got this macro that made a backup of my invoice and renamed it, but now instead of making a backup copy, it renames my original Invoice copy. Sub Make_Bkup_Copy() On Error Resume Next ActiveSheet.Copy after:=lastSheet Set ws = ActiveSheet ws.Name = Range("K2") & "-" & Range("G1") With Target If .Value < "" Then Name = .Value End If End With Sheets("Summary").Columns("A:F").fmTextAlign , 2 Sheets("Summary").Move befo=Sheets("Facture") Sheets("Facture").Select Range("K2").Select End Sub It should copy my Invoice , rename the Tab with cell K2 and G1 ( Invoice number with name) Any help would be appreciated. Thank you Cimjet |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Sheet and rename it.
Also.............if you run the macro twice on the same invoice sheet, the On
Error Resume Next will prevent the error message about two sheets with same name. i.e. if you run the macro twice on Invoice sheet, the second run will produce a new sheet named Invoice(2) instead of your concatenated name of ws.Name = Range("K2") & "-" & Range("G1") Gord On Tue, 07 Jun 2011 07:28:19 -0700, Gord Dibben wrote: If this code ever worked as you say, then you must have made a change to the code at some point. You have not set lastSheet Sub Make_Bkup_Copy() Dim lastSheet As Worksheet Dim ws as Worksheet Set lastSheet = Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Copy after:=lastSheet Set ws = ActiveSheet ws.Name = Range("K2") & "-" & Range("G1") 'what is this part designed to do? ' With Target ' If .Value < "" Then ' Name = .Value ' End If ' End With Sheets("Summary").Columns("A:F").fmTextAlign , 2 Sheets("Summary").Move befo=Sheets("Facture") Sheets("Facture").Select Range("K2").Select End Sub Gord Dibben MS Excel MVP On Tue, 7 Jun 2011 08:36:46 -0400, "Cimjet" wrote: Hi Everyone I've got this macro that made a backup of my invoice and renamed it, but now instead of making a backup copy, it renames my original Invoice copy. Sub Make_Bkup_Copy() On Error Resume Next ActiveSheet.Copy after:=lastSheet Set ws = ActiveSheet ws.Name = Range("K2") & "-" & Range("G1") With Target If .Value < "" Then Name = .Value End If End With Sheets("Summary").Columns("A:F").fmTextAlign , 2 Sheets("Summary").Move befo=Sheets("Facture") Sheets("Facture").Select Range("K2").Select End Sub It should copy my Invoice , rename the Tab with cell K2 and G1 ( Invoice number with name) Any help would be appreciated. Thank you Cimjet |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Sheet and rename it.
Hi Gord
Thank you for helping me, it works fine. You ask: 'what is this part designed to do? It must of slip in because I played a lot with the macro and was not careful. Thanks again Gord Cimjet "Gord Dibben" wrote in message ... Also.............if you run the macro twice on the same invoice sheet, the On Error Resume Next will prevent the error message about two sheets with same name. i.e. if you run the macro twice on Invoice sheet, the second run will produce a new sheet named Invoice(2) instead of your concatenated name of ws.Name = Range("K2") & "-" & Range("G1") Gord On Tue, 07 Jun 2011 07:28:19 -0700, Gord Dibben wrote: If this code ever worked as you say, then you must have made a change to the code at some point. You have not set lastSheet Sub Make_Bkup_Copy() Dim lastSheet As Worksheet Dim ws as Worksheet Set lastSheet = Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Copy after:=lastSheet Set ws = ActiveSheet ws.Name = Range("K2") & "-" & Range("G1") 'what is this part designed to do? ' With Target ' If .Value < "" Then ' Name = .Value ' End If ' End With Sheets("Summary").Columns("A:F").fmTextAlign , 2 Sheets("Summary").Move befo=Sheets("Facture") Sheets("Facture").Select Range("K2").Select End Sub Gord Dibben MS Excel MVP On Tue, 7 Jun 2011 08:36:46 -0400, "Cimjet" wrote: Hi Everyone I've got this macro that made a backup of my invoice and renamed it, but now instead of making a backup copy, it renames my original Invoice copy. Sub Make_Bkup_Copy() On Error Resume Next ActiveSheet.Copy after:=lastSheet Set ws = ActiveSheet ws.Name = Range("K2") & "-" & Range("G1") With Target If .Value < "" Then Name = .Value End If End With Sheets("Summary").Columns("A:F").fmTextAlign , 2 Sheets("Summary").Move befo=Sheets("Facture") Sheets("Facture").Select Range("K2").Select End Sub It should copy my Invoice , rename the Tab with cell K2 and G1 ( Invoice number with name) Any help would be appreciated. Thank you Cimjet |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Sheet and rename it.
Happy to assist.
Gord On Tue, 7 Jun 2011 11:08:45 -0400, "Cimjet" wrote: Hi Gord Thank you for helping me, it works fine. You ask: 'what is this part designed to do? It must of slip in because I played a lot with the macro and was not careful. Thanks again Gord Cimjet "Gord Dibben" wrote in message .. . Also.............if you run the macro twice on the same invoice sheet, the On Error Resume Next will prevent the error message about two sheets with same name. i.e. if you run the macro twice on Invoice sheet, the second run will produce a new sheet named Invoice(2) instead of your concatenated name of ws.Name = Range("K2") & "-" & Range("G1") Gord On Tue, 07 Jun 2011 07:28:19 -0700, Gord Dibben wrote: If this code ever worked as you say, then you must have made a change to the code at some point. You have not set lastSheet Sub Make_Bkup_Copy() Dim lastSheet As Worksheet Dim ws as Worksheet Set lastSheet = Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Copy after:=lastSheet Set ws = ActiveSheet ws.Name = Range("K2") & "-" & Range("G1") 'what is this part designed to do? ' With Target ' If .Value < "" Then ' Name = .Value ' End If ' End With Sheets("Summary").Columns("A:F").fmTextAlign , 2 Sheets("Summary").Move befo=Sheets("Facture") Sheets("Facture").Select Range("K2").Select End Sub Gord Dibben MS Excel MVP On Tue, 7 Jun 2011 08:36:46 -0400, "Cimjet" wrote: Hi Everyone I've got this macro that made a backup of my invoice and renamed it, but now instead of making a backup copy, it renames my original Invoice copy. Sub Make_Bkup_Copy() On Error Resume Next ActiveSheet.Copy after:=lastSheet Set ws = ActiveSheet ws.Name = Range("K2") & "-" & Range("G1") With Target If .Value < "" Then Name = .Value End If End With Sheets("Summary").Columns("A:F").fmTextAlign , 2 Sheets("Summary").Move befo=Sheets("Facture") Sheets("Facture").Select Range("K2").Select End Sub It should copy my Invoice , rename the Tab with cell K2 and G1 ( Invoice number with name) Any help would be appreciated. Thank you Cimjet |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Sheet and rename it.
Hi Gord
I'm having one problem with your macro. If the name is too long, it doesn't use my concatenated name instead it names the tab "Invoice2", etc.. Just to clarify what I'm doing... The concatenated name is the invoice number with the customer name. Since the invoice number always changes, it never copies the same name twice. E.g. if the name is (GENFOOT INC ) I can have as many copies as I like but if the name is longer like this GENFOOT INC. - DIVISION IMD (Kamik) it won't print one tab with that name, I get Invoice2 instead. I tried different names and it always the same. I didn't check how long I can go but maybe you may know the problem. Regards Cimjet "Gord Dibben" wrote in message ... Happy to assist. Gord On Tue, 7 Jun 2011 11:08:45 -0400, "Cimjet" wrote: Hi Gord Thank you for helping me, it works fine. You ask: 'what is this part designed to do? It must of slip in because I played a lot with the macro and was not careful. Thanks again Gord Cimjet "Gord Dibben" wrote in message . .. Also.............if you run the macro twice on the same invoice sheet, the On Error Resume Next will prevent the error message about two sheets with same name. i.e. if you run the macro twice on Invoice sheet, the second run will produce a new sheet named Invoice(2) instead of your concatenated name of ws.Name = Range("K2") & "-" & Range("G1") Gord On Tue, 07 Jun 2011 07:28:19 -0700, Gord Dibben wrote: If this code ever worked as you say, then you must have made a change to the code at some point. You have not set lastSheet Sub Make_Bkup_Copy() Dim lastSheet As Worksheet Dim ws as Worksheet Set lastSheet = Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Copy after:=lastSheet Set ws = ActiveSheet ws.Name = Range("K2") & "-" & Range("G1") 'what is this part designed to do? ' With Target ' If .Value < "" Then ' Name = .Value ' End If ' End With Sheets("Summary").Columns("A:F").fmTextAlign , 2 Sheets("Summary").Move befo=Sheets("Facture") Sheets("Facture").Select Range("K2").Select End Sub Gord Dibben MS Excel MVP On Tue, 7 Jun 2011 08:36:46 -0400, "Cimjet" wrote: Hi Everyone I've got this macro that made a backup of my invoice and renamed it, but now instead of making a backup copy, it renames my original Invoice copy. Sub Make_Bkup_Copy() On Error Resume Next ActiveSheet.Copy after:=lastSheet Set ws = ActiveSheet ws.Name = Range("K2") & "-" & Range("G1") With Target If .Value < "" Then Name = .Value End If End With Sheets("Summary").Columns("A:F").fmTextAlign , 2 Sheets("Summary").Move befo=Sheets("Facture") Sheets("Facture").Select Range("K2").Select End Sub It should copy my Invoice , rename the Tab with cell K2 and G1 ( Invoice number with name) Any help would be appreciated. Thank you Cimjet |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy sheet and rename | Excel Programming | |||
| How to copy a sheet and rename it with the value of two cells from the source sheet? | Excel Programming | |||
| Copy/Rename a sheet | Links and Linking in Excel | |||
| Button to copy sheet, rename sheet sequencially. | Excel Programming | |||
| Copy a sheet and rename it | Excel Programming | |||
![Cimjet[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
