Copying Worksheet to another Workbook - ERROR

[MAdcock]

Hi,

I have created an excel program which creates 65 or more (nneds to be unlimited) customised letters for clients. The macro uses VLOOKUP to change the details within the letter and copies / moves the work sheet to another "template file" which contains printing macros etc. Each new worksheet is subsequently renamed to the Client ID reference.

The program is meant to do all applicable clients letters and then saves the template accordingly. Unfortunately for some reason the macro will run perfectly one time and then the next will not copy once it reaches a random client, or do I mean just doesn't do the copy.

The macro does not stop and provide the dreaded excel error message at all. The macro simply carries on the loop, changing the letters details etc but not copying the worksheet to the template file. Once the maco has finished, even if I try a manual move/copy it simply does not do it! is this just one of those extremely annyoing things with Excel? If i close excel and start up again and run the same macro, the macro will either complete as required or do exactly the same again. When I initially created the macro all worked fine!

Is there a way of forcing the copy to happen or checking that each worksheet has been created in the template file?

Your help is much appreciated.

My code is below:

_______________ _______________ _____

[CODE=VB]
Loop Macro that goes down list of clients changing the VLOOKUP on D34 on CONTROL tab
_______________ _______________ _____

Sheets("CLIENT HOLDINGS").Sele ct
Range("A2").Sel ect

' Hide Rows with blanks for Addressing part of statement
On Error Resume Next
Do Until ActiveCell.Valu e = ""
If ActiveCell.Valu e <> "" Then
ActiveCell.Copy
Sheets("CONTROL ").Select
Range("D34").Se lect
Selection.Paste Special Paste:=xlPasteV alues, Operation:=xlNo ne, SkipBlanks _
:=False, Transpose:=Fals e

Sheets("Cache") .Select
'Range("B23").S elect

If Range("B23").Va lue <> "0" Then

Call TemplateCover
Windows("Distri bution Statements(ORIG INAL).xls").Act ivate

Else:

End If
Sheets("CLIENT HOLDINGS").Sele ct
ActiveCell.Offs et(1, 0).Select

Else:

End If
Loop


_______________ _______________ _____
TemplateCover macro (I8 = Client ID)
_______________ _______________ _____

Sheets("TEMPLAT E").Copy After:=Workbook s("Template Cover.xls").She ets(1)
Cells.Select
Selection.Copy
Selection.Paste Special Paste:=xlPasteV alues, Operation:=xlNo ne, SkipBlanks _
:=False, Transpose:=Fals e

Sheets("TEMPLAT E").Name = Range("I8").Val ue

End Sub[/CODE]

[ubentook]

Your use of "On Error Resume Next" overrides any errors generated.
You should move the line... "ActiveCell.Off set(1, 0).Select"
downward to just before the word Loop.

Hi,

I have created an excel program which creates 65 or more (nneds to be unlimited) customised letters for clients. The macro uses VLOOKUP to change the details within the letter and copies / moves the work sheet to another "template file" which contains printing macros etc. Each new worksheet is subsequently renamed to the Client ID reference.

The program is meant to do all applicable clients letters and then saves the template accordingly. Unfortunately for some reason the macro will run perfectly one time and then the next will not copy once it reaches a random client, or do I mean just doesn't do the copy.

The macro does not stop and provide the dreaded excel error message at all. The macro simply carries on the loop, changing the letters details etc but not copying the worksheet to the template file. Once the maco has finished, even if I try a manual move/copy it simply does not do it! is this just one of those extremely annyoing things with Excel? If i close excel and start up again and run the same macro, the macro will either complete as required or do exactly the same again. When I initially created the macro all worked fine!

Is there a way of forcing the copy to happen or checking that each worksheet has been created in the template file?

Your help is much appreciated.

My code is below:

_______________ _______________ _____

[CODE=VB]
Loop Macro that goes down list of clients changing the VLOOKUP on D34 on CONTROL tab
_______________ _______________ _____

Sheets("CLIENT HOLDINGS").Sele ct
Range("A2").Sel ect

' Hide Rows with blanks for Addressing part of statement
On Error Resume Next
Do Until ActiveCell.Valu e = ""
If ActiveCell.Valu e <> "" Then
ActiveCell.Copy
Sheets("CONTROL ").Select
Range("D34").Se lect
Selection.Paste Special Paste:=xlPasteV alues, Operation:=xlNo ne, SkipBlanks _
:=False, Transpose:=Fals e

Sheets("Cache") .Select
'Range("B23").S elect

If Range("B23").Va lue <> "0" Then

Call TemplateCover
Windows("Distri bution Statements(ORIG INAL).xls").Act ivate

Else:

End If
Sheets("CLIENT HOLDINGS").Sele ct
ActiveCell.Offs et(1, 0).Select

Else:

End If
Loop


_______________ _______________ _____
TemplateCover macro (I8 = Client ID)
_______________ _______________ _____

Sheets("TEMPLAT E").Copy After:=Workbook s("Template Cover.xls").She ets(1)
Cells.Select
Selection.Copy
Selection.Paste Special Paste:=xlPasteV alues, Operation:=xlNo ne, SkipBlanks _
:=False, Transpose:=Fals e

Sheets("TEMPLAT E").Name = Range("I8").Val ue

End Sub[/CODE]

[MAdcock]

Thanks for your input but that does not solve the problem. By removing the "On error" line it produces the error message. I need the activecell.offs et to move down the list of client ID references.

Excel seems to randomly decide when to be unable to copy/move the worksheet from one workbook another (template).

Still require help if anybody has an idea?

[kadghar]

Thanks for your input but that does not solve the problem. By removing the "On error" line it produces the error message. I need the activecell.offs et to move down the list of client ID references.

Excel seems to randomly decide when to be unable to copy/move the worksheet from one workbook another (template).

Still require help if anybody has an idea?

Have in mind that you cannot copy-paste to a worksheet-workbook that is not visible, or active. Try activating the worksheet, workbook bevore pasting. Or instead of copy-paste, define a variable, save the value into the variable, and then write the variable into the other worksheet. This way, the sheets dont have to be (not even) visible.

I'll recomend you to delete a worksheet you've already printed, for you not to have an overflow.

[Dököll]

Hi,

...I try a manual move/copy it simply does not do it! is this just one of those extremely annyoing things with Excel? If i close excel and start up again and run the same macro, the macro will either complete as required or do exactly the same again. When I initially created the macro all worked fine!

Is there a way of forcing the copy to happen or checking that each worksheet has been created in the template file?

Your help is much appreciated.

My code is below:

_______________ _______________ _____

[CODE=VB]
Loop Macro that goes down list of clients changing the VLOOKUP on D34 on CONTROL tab
_______________ _______________ _____

Sheets("CLIENT HOLDINGS").Sele ct
Range("A2").Sel ect

' Hide Rows with blanks for Addressing part of statement
On Error Resume Next
Do Until ActiveCell.Valu e = ""
If ActiveCell.Valu e <> "" Then
ActiveCell.Copy
Sheets("CONTROL ").Select
Range("D34").Se lect
Selection.Paste Special Paste:=xlPasteV alues, Operation:=xlNo ne, SkipBlanks _
:=False, Transpose:=Fals e

Sheets("Cache") .Select
'Range("B23").S elect

If Range("B23").Va lue <> "0" Then

Call TemplateCover
Windows("Distri bution Statements(ORIG INAL).xls").Act ivate

Else:

End If
Sheets("CLIENT HOLDINGS").Sele ct
ActiveCell.Offs et(1, 0).Select

Else:

End If
Loop


_______________ _______________ _____
TemplateCover macro (I8 = Client ID)
_______________ _______________ _____

Sheets("TEMPLAT E").Copy After:=Workbook s("Template Cover.xls").She ets(1)
Cells.Select
Selection.Copy
Selection.Paste Special Paste:=xlPasteV alues, Operation:=xlNo ne, SkipBlanks _
:=False, Transpose:=Fals e

Sheets("TEMPLAT E").Name = Range("I8").Val ue

End Sub[/CODE]

Hey there MAdcock!

Thank you for submitting your code, and it's likely you have previously searched our forum and if you have not as of yet, please do or Google your title, see what pops up.

Also if you tell us whether a different way of achieving what you need would do just fine, let us know. Sorry for your troubles...

Dököll