Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/pastespecial error
I have the following code and am trying to copy and paste special values to
another sheet in another workbook (in the next empty row). There are 45 columns (1 row) of data being pasted. It runs successfully half the time and with the remainder I get an error as though my clipboard is empty (PasteSpecial method of Range class failed). Does anyone know what could be wrong with my code? Thanks! Sub savedata() CoCo = 1222 Stamp = Format(Now, "mm/dd/yyyy Hh:mm:ss") Range("Q1").Select Selection.CurrentRegion.Select Selection.Copy Workbooks.Open Filename:= _ "S:\USS Financial Services\Accounting Services\Completed Recons\Cash Management\" & CoCo & "-Recons Current Month.xls" ActiveSheet.Activate ActiveSheet.Unprotect Password:="xxxxxxx" Range("B3").Select With Selection.CurrentRegion .Cells(.Cells.Count).Activate End With ActiveCell.Offset(Count + 1, -45).Select Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveCell.Offset(Count + 0, 45).Select ActiveCell.Value = Stamp ActiveCell.Offset(Count + 1, -45).Select ActiveSheet.Protect Password:="xxxxxxx" ActiveWorkbook.Save ActiveWorkbook.Close End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/pastespecial error
Hello,
You are doing Selection.Copy but then pasting waay down below, it's possible the clipboard is being emptied in the middle of your macro since you have so many statements in between them. HTH, JP On Oct 26, 4:25 pm, Jon wrote: I have the following code and am trying to copy and paste special values to another sheet in another workbook (in the next empty row). There are 45 columns (1 row) of data being pasted. It runs successfully half the time and with the remainder I get an error as though my clipboard is empty (PasteSpecial method of Range class failed). Does anyone know what could be wrong with my code? Thanks! Sub savedata() CoCo = 1222 Stamp = Format(Now, "mm/dd/yyyy Hh:mm:ss") Range("Q1").Select Selection.CurrentRegion.Select Selection.Copy Workbooks.Open Filename:= _ "S:\USS Financial Services\Accounting Services\Completed Recons\Cash Management\" & CoCo & "-Recons Current Month.xls" ActiveSheet.Activate ActiveSheet.Unprotect Password:="xxxxxxx" Range("B3").Select With Selection.CurrentRegion .Cells(.Cells.Count).Activate End With ActiveCell.Offset(Count + 1, -45).Select Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveCell.Offset(Count + 0, 45).Select ActiveCell.Value = Stamp ActiveCell.Offset(Count + 1, -45).Select ActiveSheet.Protect Password:="xxxxxxx" ActiveWorkbook.Save ActiveWorkbook.Close End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/pastespecial error
Hi jon,
Even in the interactive mode I have sometimes "lost" copied data from the clipboard when performing other operations. Try opening the other workbook first and then do the copy and paste without other code in between. Just an observation but 'ActiveSheet.Activate' seem superflourous. The sheet is already the active sheet. Regards, OssieMac "Jon" wrote: I have the following code and am trying to copy and paste special values to another sheet in another workbook (in the next empty row). There are 45 columns (1 row) of data being pasted. It runs successfully half the time and with the remainder I get an error as though my clipboard is empty (PasteSpecial method of Range class failed). Does anyone know what could be wrong with my code? Thanks! Sub savedata() CoCo = 1222 Stamp = Format(Now, "mm/dd/yyyy Hh:mm:ss") Range("Q1").Select Selection.CurrentRegion.Select Selection.Copy Workbooks.Open Filename:= _ "S:\USS Financial Services\Accounting Services\Completed Recons\Cash Management\" & CoCo & "-Recons Current Month.xls" ActiveSheet.Activate ActiveSheet.Unprotect Password:="xxxxxxx" Range("B3").Select With Selection.CurrentRegion .Cells(.Cells.Count).Activate End With ActiveCell.Offset(Count + 1, -45).Select Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveCell.Offset(Count + 0, 45).Select ActiveCell.Value = Stamp ActiveCell.Offset(Count + 1, -45).Select ActiveSheet.Protect Password:="xxxxxxx" ActiveWorkbook.Save ActiveWorkbook.Close End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/pastespecial error
After opening the new workbook that you are going to paste the data into,
you have the following code: Range("B3").Select With Selection.CurrentRegion .Cells(.Cells.Count).Activate End With Then the line right after that is: ActiveCell.Offset(Count + 1, -45).Select I don't think "Count" is being assigned anything (it appears to be a new variable that has never been assigned any value). If you single-step through the code, what value does this "Count" have when you hover the mouse over it? -- Regards, Bill Renaud |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/pastespecial error
OssieMac:
Thanks-I rearranged my code to move the copy & paste operations much closer. It did the trick! Thanks to JP as well! Jon "OssieMac" wrote: Hi jon, Even in the interactive mode I have sometimes "lost" copied data from the clipboard when performing other operations. Try opening the other workbook first and then do the copy and paste without other code in between. Just an observation but 'ActiveSheet.Activate' seem superflourous. The sheet is already the active sheet. Regards, OssieMac "Jon" wrote: I have the following code and am trying to copy and paste special values to another sheet in another workbook (in the next empty row). There are 45 columns (1 row) of data being pasted. It runs successfully half the time and with the remainder I get an error as though my clipboard is empty (PasteSpecial method of Range class failed). Does anyone know what could be wrong with my code? Thanks! Sub savedata() CoCo = 1222 Stamp = Format(Now, "mm/dd/yyyy Hh:mm:ss") Range("Q1").Select Selection.CurrentRegion.Select Selection.Copy Workbooks.Open Filename:= _ "S:\USS Financial Services\Accounting Services\Completed Recons\Cash Management\" & CoCo & "-Recons Current Month.xls" ActiveSheet.Activate ActiveSheet.Unprotect Password:="xxxxxxx" Range("B3").Select With Selection.CurrentRegion .Cells(.Cells.Count).Activate End With ActiveCell.Offset(Count + 1, -45).Select Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveCell.Offset(Count + 0, 45).Select ActiveCell.Value = Stamp ActiveCell.Offset(Count + 1, -45).Select ActiveSheet.Protect Password:="xxxxxxx" ActiveWorkbook.Save ActiveWorkbook.Close End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/pastespecial error
Bill,
Thanks for the thoughts. The first part of the code you mention selects a large, variable, contiguous portion of my sheet and always selects the bottom right cell of the range. The ActiveCell.Offset(Count + 1, -45).Select < portion simply moves the active cell down 1 row and 45 columns to the left so I can paste in a new row of data. The "Count" is more of an instruction to move the designated number of columns/rows. Thanks! Jon "Bill Renaud" wrote: After opening the new workbook that you are going to paste the data into, you have the following code: Range("B3").Select With Selection.CurrentRegion .Cells(.Cells.Count).Activate End With Then the line right after that is: ActiveCell.Offset(Count + 1, -45).Select I don't think "Count" is being assigned anything (it appears to be a new variable that has never been assigned any value). If you single-step through the code, what value does this "Count" have when you hover the mouse over it? -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and PasteSpecial help | Excel Programming | |||
Error in PasteSpecial ? | Excel Programming | |||
PasteSpecial error | Excel Programming | |||
Copy PasteSpecial | Excel Programming | |||
PasteSpecial Error | Excel Programming |