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

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

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

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

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

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

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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

On Fri, 10 Jun 2011 09:10:31 -0400, "Cimjet" wrote:

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

Thanks, Gord! I knew there was a limit but wasn't sure how many
characters it was. As usual, your expertise is always appreciated...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

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

On Fri, 10 Jun 2011 09:10:31 -0400, "Cimjet" wrote:

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
m...
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

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Actually it is 31 chars max...........not 30

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


Gord

On Fri, 10 Jun 2011 15:20:09 -0400, "Cimjet" wrote:

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

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

On Fri, 10 Jun 2011 09:10:31 -0400, "Cimjet" wrote:

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
om...
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