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

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 <J...@discussions.microsoft.com> 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

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

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

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

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