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:

___________________________________

Loop Macro that goes down list of clients changing the VLOOKUP on D34 on CONTROL tab
___________________________________
 
Sheets("CLIENT HOLDINGS").Select
Range("A2").Select
 
' Hide Rows with blanks for Addressing part of statement
On Error Resume Next
Do Until ActiveCell.Value = ""
If ActiveCell.Value <> "" Then
    ActiveCell.Copy
    Sheets("CONTROL").Select
    Range("D34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    Sheets("Cache").Select
    'Range("B23").Select
 
        If Range("B23").Value <> "0" Then
 
            Call TemplateCover
            Windows("Distribution Statements(ORIGINAL).xls").Activate
 
        Else:
 
        End If
    Sheets("CLIENT HOLDINGS").Select
    ActiveCell.Offset(1, 0).Select
 
Else:
 
End If
Loop
 
 
___________________________________
TemplateCover macro (I8 = Client ID)
___________________________________
 
Sheets("TEMPLATE").Copy After:=Workbooks("Template Cover.xls").Sheets(1)
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    Sheets("TEMPLATE").Name = Range("I8").Value
 
End Sub

[ubentook]

Your use of "On Error Resume Next" overrides any errors generated.
You should move the line... "ActiveCell.Offset(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:

___________________________________

Loop Macro that goes down list of clients changing the VLOOKUP on D34 on CONTROL tab
___________________________________
 
Sheets("CLIENT HOLDINGS").Select
Range("A2").Select
 
' Hide Rows with blanks for Addressing part of statement
On Error Resume Next
Do Until ActiveCell.Value = ""
If ActiveCell.Value <> "" Then
    ActiveCell.Copy
    Sheets("CONTROL").Select
    Range("D34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    Sheets("Cache").Select
    'Range("B23").Select
 
        If Range("B23").Value <> "0" Then
 
            Call TemplateCover
            Windows("Distribution Statements(ORIGINAL).xls").Activate
 
        Else:
 
        End If
    Sheets("CLIENT HOLDINGS").Select
    ActiveCell.Offset(1, 0).Select
 
Else:
 
End If
Loop
 
 
___________________________________
TemplateCover macro (I8 = Client ID)
___________________________________
 
Sheets("TEMPLATE").Copy After:=Workbooks("Template Cover.xls").Sheets(1)
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    Sheets("TEMPLATE").Name = Range("I8").Value
 
End Sub

[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.offset 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.offset 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:

___________________________________

Loop Macro that goes down list of clients changing the VLOOKUP on D34 on CONTROL tab
___________________________________
 
Sheets("CLIENT HOLDINGS").Select
Range("A2").Select
 
' Hide Rows with blanks for Addressing part of statement
On Error Resume Next
Do Until ActiveCell.Value = ""
If ActiveCell.Value <> "" Then
    ActiveCell.Copy
    Sheets("CONTROL").Select
    Range("D34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    Sheets("Cache").Select
    'Range("B23").Select
 
        If Range("B23").Value <> "0" Then
 
            Call TemplateCover
            Windows("Distribution Statements(ORIGINAL).xls").Activate
 
        Else:
 
        End If
    Sheets("CLIENT HOLDINGS").Select
    ActiveCell.Offset(1, 0).Select
 
Else:
 
End If
Loop
 
 
___________________________________
TemplateCover macro (I8 = Client ID)
___________________________________
 
Sheets("TEMPLATE").Copy After:=Workbooks("Template Cover.xls").Sheets(1)
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    Sheets("TEMPLATE").Name = Range("I8").Value
 
End Sub

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