By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,828 Members | 2,241 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,828 IT Pros & Developers. It's quick & easy.

Copying Worksheet to another Workbook - ERROR

P: 13
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:

___________________________________

Expand|Select|Wrap|Line Numbers
  1. Loop Macro that goes down list of clients changing the VLOOKUP on D34 on CONTROL tab
  2. ___________________________________
  3.  
  4. Sheets("CLIENT HOLDINGS").Select
  5. Range("A2").Select
  6.  
  7. ' Hide Rows with blanks for Addressing part of statement
  8. On Error Resume Next
  9. Do Until ActiveCell.Value = ""
  10. If ActiveCell.Value <> "" Then
  11.     ActiveCell.Copy
  12.     Sheets("CONTROL").Select
  13.     Range("D34").Select
  14.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  15.         :=False, Transpose:=False
  16.  
  17.     Sheets("Cache").Select
  18.     'Range("B23").Select
  19.  
  20.         If Range("B23").Value <> "0" Then
  21.  
  22.             Call TemplateCover
  23.             Windows("Distribution Statements(ORIGINAL).xls").Activate
  24.  
  25.         Else:
  26.  
  27.         End If
  28.     Sheets("CLIENT HOLDINGS").Select
  29.     ActiveCell.Offset(1, 0).Select
  30.  
  31. Else:
  32.  
  33. End If
  34. Loop
  35.  
  36.  
  37. ___________________________________
  38. TemplateCover macro (I8 = Client ID)
  39. ___________________________________
  40.  
  41. Sheets("TEMPLATE").Copy After:=Workbooks("Template Cover.xls").Sheets(1)
  42.     Cells.Select
  43.     Selection.Copy
  44.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  45.         :=False, Transpose:=False
  46.  
  47.     Sheets("TEMPLATE").Name = Range("I8").Value
  48.  
  49. End Sub
Apr 30 '08 #1
Share this Question
Share on Google+
4 Replies


P: 58
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:

___________________________________

Expand|Select|Wrap|Line Numbers
  1. Loop Macro that goes down list of clients changing the VLOOKUP on D34 on CONTROL tab
  2. ___________________________________
  3.  
  4. Sheets("CLIENT HOLDINGS").Select
  5. Range("A2").Select
  6.  
  7. ' Hide Rows with blanks for Addressing part of statement
  8. On Error Resume Next
  9. Do Until ActiveCell.Value = ""
  10. If ActiveCell.Value <> "" Then
  11.     ActiveCell.Copy
  12.     Sheets("CONTROL").Select
  13.     Range("D34").Select
  14.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  15.         :=False, Transpose:=False
  16.  
  17.     Sheets("Cache").Select
  18.     'Range("B23").Select
  19.  
  20.         If Range("B23").Value <> "0" Then
  21.  
  22.             Call TemplateCover
  23.             Windows("Distribution Statements(ORIGINAL).xls").Activate
  24.  
  25.         Else:
  26.  
  27.         End If
  28.     Sheets("CLIENT HOLDINGS").Select
  29.     ActiveCell.Offset(1, 0).Select
  30.  
  31. Else:
  32.  
  33. End If
  34. Loop
  35.  
  36.  
  37. ___________________________________
  38. TemplateCover macro (I8 = Client ID)
  39. ___________________________________
  40.  
  41. Sheets("TEMPLATE").Copy After:=Workbooks("Template Cover.xls").Sheets(1)
  42.     Cells.Select
  43.     Selection.Copy
  44.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  45.         :=False, Transpose:=False
  46.  
  47.     Sheets("TEMPLATE").Name = Range("I8").Value
  48.  
  49. End Sub
May 1 '08 #2

P: 13
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?
May 1 '08 #3

kadghar
Expert 100+
P: 1,295
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.
May 2 '08 #4

Dököll
Expert 100+
P: 2,364
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:

___________________________________

Expand|Select|Wrap|Line Numbers
  1. Loop Macro that goes down list of clients changing the VLOOKUP on D34 on CONTROL tab
  2. ___________________________________
  3.  
  4. Sheets("CLIENT HOLDINGS").Select
  5. Range("A2").Select
  6.  
  7. ' Hide Rows with blanks for Addressing part of statement
  8. On Error Resume Next
  9. Do Until ActiveCell.Value = ""
  10. If ActiveCell.Value <> "" Then
  11.     ActiveCell.Copy
  12.     Sheets("CONTROL").Select
  13.     Range("D34").Select
  14.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  15.         :=False, Transpose:=False
  16.  
  17.     Sheets("Cache").Select
  18.     'Range("B23").Select
  19.  
  20.         If Range("B23").Value <> "0" Then
  21.  
  22.             Call TemplateCover
  23.             Windows("Distribution Statements(ORIGINAL).xls").Activate
  24.  
  25.         Else:
  26.  
  27.         End If
  28.     Sheets("CLIENT HOLDINGS").Select
  29.     ActiveCell.Offset(1, 0).Select
  30.  
  31. Else:
  32.  
  33. End If
  34. Loop
  35.  
  36.  
  37. ___________________________________
  38. TemplateCover macro (I8 = Client ID)
  39. ___________________________________
  40.  
  41. Sheets("TEMPLATE").Copy After:=Workbooks("Template Cover.xls").Sheets(1)
  42.     Cells.Select
  43.     Selection.Copy
  44.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  45.         :=False, Transpose:=False
  46.  
  47.     Sheets("TEMPLATE").Name = Range("I8").Value
  48.  
  49. 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
May 3 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.