Copy Sheet and rename it.

  • Thread starter Thread starter Cimjet
  • Start date Start date
C

Cimjet

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 before:=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
 
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 before:=Sheets("Facture")
Sheets("Facture").Select
Range("K2").Select

End Sub


Gord Dibben MS Excel MVP
 
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
 
Hi Gord
Thank you for helping me, it works fine.
You ask:It must of slip in because I played a lot with the macro and was not careful.
Thanks again Gord
Cimjet
 
Happy to assist.


Gord

Hi Gord
Thank you for helping me, it works fine.
You ask:
It must of slip in because I played a lot with the macro and was not careful.
Thanks again Gord
Cimjet
Click to expand...
 
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
 
I think there might be a limit to the number of characters you can use
for sheetnames. Also, when renaming sheets via VBA with names that
contain spaces or other non alpha-numeric characters, the name should
be wrapped in apostrophes.

Your methodology for naming sheets as per customer/invoice number is
probably not the best way to handle this. Not saying there's anything
wrong with doing it this way, though if you want to persist I suggest
you reduce the customer name portion to a customer code abbreviation or
some other truncated methodology like this:

GENFOOT INC. - DIVISION IMD (Kamik)
could be truncated to...
GENFOOT-IMD_<Inv#>
or...
GENINC-DIVIMD_<Inv#> if the above isn't enough to identify the sheet.
 
The problem is that Excel sheet names can be no longer than 30 characters.

GENFOOT INC. - DIVISION IMD (Kamik) is 35 chars so you will get an error
but.............

You get Invoice2 because of the Resume Next

If you would rem out Resume Next it would become apparent you have an error.


Gord
 
Thanks, Gord! I knew there was a limit but wasn't sure how many
characters it was. As usual, your expertise is always appreciated...
 
Hi Gord
Thank you again, you're right about the resume next, I now realized with my own
experience what was said from the beginning.
Live&learn.
Great support.
cimjet
 
Hi Garry
Yes, Gord just tool me 30 characters max. I will create a column with
abbreviated names.
Great support from you guys.
Thanks
Cimjet
 
Actually it is 31 chars max...........not 30

That's about how I learn everything..........from mistakes<g>


Gord
 
Back
Top