Trouble pasting

S

Sandy

Excel 2003
I have the following which fails with Runtime error 438 at the asterisked
line.
Both books are open but both sheets in OldBook and NewBook are hidden.

Thanks
Sandy

Windows(OldBook).Activate

With Workbooks(OldBook).Sheets("RecordOfRounds")
Lr = .Range("A65536").End(xlUp).Row
.Unprotect Password:="xxxxx"
.Range("A53:GM" & Lr).Copy
.Protect Password:="xxxxx"
End With

Windows(NewBook).Activate

With Workbooks(NewBook).Sheets("RecordOfRounds")
.Unprotect Password:="xxxxx"
.Range("A53").Paste '*************************
.Protect Password:="xxxxx"
End With
 
R

royUK

Try swiching off screen updating then unhide the sheets, do the cop
then re-hide. This is untsted


Code
-------------------
Application.ScreenUpdating = False
With Workbooks(NewBook).Sheets("RecordOfRounds")
.Unprotect Password:="xxxxx"
.Visible = xlSheetVisible
With Workbooks(OldBook).Sheets("RecordOfRounds")
.Visible = xlSheetVisible
Lr = .Range("A65536").End(xlUp).Row
.Unprotect Password:="xxxxx"
.Range("A53:GM" & Lr).Copy _
Workbooks(NewBook).Sheets("RecordOfRounds").Range("A53")
.Visible = xlSheetHidden
.Protect Password:="xxxxx"
End With
.Protect Password:="xxxxx"
End With
Application.ScreenUpdating = Tru
-------------------

--
royU

Hope that helps, RoyUK
For tips & examples visit my 'web site
' (http://www.excel-it.com
 
J

Jim Cone

..Paste .Range("A53")
--
Jim Cone
Portland, Oregon USA



"Sandy"
wrote in message
Excel 2003
I have the following which fails with Runtime error 438 at the asterisked
line.
Both books are open but both sheets in OldBook and NewBook are hidden.
Thanks
Sandy

Windows(OldBook).Activate
With Workbooks(OldBook).Sheets("RecordOfRounds")
Lr = .Range("A65536").End(xlUp).Row
.Unprotect Password:="xxxxx"
.Range("A53:GM" & Lr).Copy
.Protect Password:="xxxxx"
End With

Windows(NewBook).Activate
With Workbooks(NewBook).Sheets("RecordOfRounds")
.Unprotect Password:="xxxxx"
.Range("A53").Paste '*************************
.Protect Password:="xxxxx"
End With
 
S

Sandy

Hi Jim,

Get a runtime error 1004 now:- "Unable to get the Paste property of the
Worksheet class".
Have pasted the entire sub, which is in a module in 'NewBook'.
NewBook name = "Personal Data V2-Sandy.xls"

Sandy

Sub TransferData()

Dim NewBookPath As String
Dim OldBookPath As String
Dim OldBook As String
Dim NewBook As String
Dim Lr As Long

NewBookPath = ThisWorkbook.Path
OldBookPath = NewBookPath & "\Personal Data-Sandy.xls"

NewBook = ActiveWorkbook.Name
OldBook = "Personal Data-Sandy.xls"

Workbooks.Open OldBookPath

Windows(OldBook).Activate
ActiveWorkbook.Unprotect

Application.EnableEvents = False
Application.ScreenUpdating = False

With Workbooks(OldBook).Sheets("RecordOfRounds")
Lr = .Range("A65536").End(xlUp).Row
.Unprotect Password:="xxxxx"
.Visible = True
.Range("A53:GM" & Lr).Copy
.Visible = xlSheetVeryHidden
.Protect Password:="xxxxx"
End With

Windows(NewBook).Activate
ActiveWorkbook.Unprotect

With Workbooks(NewBook).Sheets("RecordOfRounds")
.Unprotect Password:="xxxxx"
.Visible = True
.Select
.Paste.Range ("A53")
.Visible = xlSheetVeryHidden
.Protect Password:="xxxxx"
End With

Windows(OldBook).Activate

Workbooks(OldBook).Close SaveChanges:=False

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
 
J

Jim Cone

A space is required between .Paste and .Range("A53")
--
Jim Cone
Portland, Oregon USA



"Sandy"
wrote in message
Hi Jim,
Get a runtime error 1004 now:- "Unable to get the Paste property of the
Worksheet class".
Have pasted the entire sub, which is in a module in 'NewBook'.
NewBook name = "Personal Data V2-Sandy.xls"
Sandy

-snip-
With Workbooks(NewBook).Sheets("RecordOfRounds")
.Unprotect Password:="xxxxx"
.Visible = True
.Select
.Paste.Range ("A53")
.Visible = xlSheetVeryHidden
.Protect Password:="xxxxx"
End With
-snip




"Jim Cone"
wrote in message
 
B

Barb Reinhardt

I've done some tweaking to see if you can figure out where the problem might
be.

Option Explicit

Sub Test()
Dim myWB As Workbook
Dim myWS As Worksheet
Dim myNewWS As Worksheet
Dim myRange As Range
Dim Lr As Long
Dim OldBook As String
Dim NewBook As String

Set myWB = Workbooks(OldBook) 'Presumably OldBook is a string

If myWB Is Nothing Then
MsgBox ("You haven't defined myWB. Execution ending.")
End
End If

Windows(OldBook).Activate


Set myWS = myWB.Worksheets("RecordOfRounds")

If myWS Is Nothing Then
MsgBox ("You haven't defined myWS. Execution ending.")
End
End If


'With myWS
Lr = myWS.Cells(myWS.Rows.Count, 1).End(xlUp).Row
myWS.Unprotect Password:="xxxxx"
Set myRange = myWS.Range("A53:GM" & Lr)
If Not myRange Is Nothing Then
Debug.Print myRange.Address
myRange.Copy
Else
MsgBox ("Range could not be copied")
End
End If
myWS.Protect Password:="xxxxx"
'End With

Set myNewWS = Workbooks(NewBook).Sheets("RecordOfRounds")
If myNewWS Is Nothing Then
MsgBox ("You haven't defined myNewWS. Execution ending.")
End
End If

Windows(NewBook).Activate

With myNewWS
.Unprotect Password:="xxxxx"
.Range("A53").Paste '*************************
.Protect Password:="xxxxx"
End With

End Sub
 
B

Barb Reinhardt

Forgot some error checking
Option Explicit

Sub Test()
Dim myWB As Workbook
Dim myWS As Worksheet
Dim myNewWS As Worksheet
Dim myRange As Range
Dim Lr As Long
Dim OldBook As String
Dim NewBook As String

On Error Resume Next
Set myWB = Workbooks(OldBook) 'Presumably OldBook is a string
On Error GoTo 0

If myWB Is Nothing Then
MsgBox ("You haven't defined myWB. Execution ending.")
End
End If

Windows(OldBook).Activate


On Error Resume Next
Set myWS = myWB.Worksheets("RecordOfRounds")
On Error GoTo 0

If myWS Is Nothing Then
MsgBox ("You haven't defined myWS. Execution ending.")
End
End If


'With myWS
Lr = myWS.Cells(myWS.Rows.Count, 1).End(xlUp).Row
myWS.Unprotect Password:="xxxxx"
On Error Resume Next

Set myRange = myWS.Range("A53:GM" & Lr)
On Error GoTo 0
If Not myRange Is Nothing Then
Debug.Print myRange.Address
myRange.Copy
Else
MsgBox ("Range could not be copied")
End
End If
myWS.Protect Password:="xxxxx"
'End With

On Error Resume Next
Set myNewWS = Workbooks(NewBook).Sheets("RecordOfRounds")
On Error GoTo 0
If myNewWS Is Nothing Then
MsgBox ("You haven't defined myNewWS. Execution ending.")
End
End If



Windows(NewBook).Activate

With myNewWS
.Unprotect Password:="xxxxx"
.Range("A53").Paste '*************************
.Protect Password:="xxxxx"
End With

End Sub
 
S

Sandy

Hi Jim,

I introduced the space but no change - same error.
There are two instances of excel open (Workbooks.Open OldBookPath) opens a
new instance - does that make a difference?

Sandy

Jim Cone said:
A space is required between .Paste and .Range("A53")
--
Jim Cone
Portland, Oregon USA



"Sandy"
wrote in message
Hi Jim,
Get a runtime error 1004 now:- "Unable to get the Paste property of the
Worksheet class".
Have pasted the entire sub, which is in a module in 'NewBook'.
NewBook name = "Personal Data V2-Sandy.xls"
Sandy

-snip-
With Workbooks(NewBook).Sheets("RecordOfRounds")
.Unprotect Password:="xxxxx"
.Visible = True
.Select
.Paste.Range ("A53")
.Visible = xlSheetVeryHidden
.Protect Password:="xxxxx"
End With
-snip




"Jim Cone"
wrote in message
Click to expand...
 
D

Dave Peterson

Most macros will destroy the clipboard. And if you have an event macro that
fires when you activate a window, that could be destroying the clipboard.

I'd use something like:

Dim RngToCopy as Range
Dim DestCell as range

With Workbooks(OldBook).Sheets("RecordOfRounds")
Lr = .Range("A65536").End(xlUp).Row
set rngtocopy = .Range("A53:GM" & Lr)
End With

With Workbooks(NewBook).Sheets("RecordOfRounds")
set destcell = .range("a53")
.Unprotect Password:="xxxxx"
rngtocopy.copy _
destination:=destcell
.Protect Password:="xxxxx"
End With

==========
But you may want to try this modification of your existing code:

.Range("A53").PasteSpecial paste:=xlpasteall

=====
ps. You paste to worksheets:
with worksheets("sheet999")
.paste destination:=.range("a53")
end with

You .pastespecial to a range.

You can use .pastespecial with a worksheet, but it serves a different purpose.
 
D

Dave Peterson

Workbooks.open ...
won't open a new instance of excel.

You may see multiple icons on the taskbar, but that's a view setting (windows in
taskbar). Both workbooks will be open in the same instance.
Hi Jim,

I introduced the space but no change - same error.
There are two instances of excel open (Workbooks.Open OldBookPath) opens a
new instance - does that make a difference?

Sandy
Click to expand...
 
S

Sandy

Hi Barb,

No messages were thrown up and the correct range showed in Debug.Print.
Still the error at the paste part though.

Sandy
 
S

Sandy

Hi Dave,

Firstly you are dead right there is only one instance - I saw two on the
taskbar and got three ;(

Secondly this works perfectly - many many thanks.
Sandy

************************************
Sub TransferData()

Dim NewBookPath As String
Dim OldBookPath As String
Dim OldBook As String
Dim NewBook As String
Dim Lr As Long
Dim RngToCopy As Range
Dim DestCell As Range

NewBookPath = ThisWorkbook.Path
OldBookPath = NewBookPath & "\Personal Data-Sandy.xls"

NewBook = ActiveWorkbook.Name

OldBook = "Personal Data-Sandy.xls"

Workbooks.Open OldBookPath

Windows(OldBook).Activate
ActiveWorkbook.Unprotect

Application.EnableEvents = False
Application.ScreenUpdating = False


With Workbooks(OldBook).Sheets("RecordOfRounds")
Lr = .Range("A65536").End(xlUp).Row
Set RngToCopy = .Range("A53:GM" & Lr)
End With

Windows(NewBook).Activate
ActiveWorkbook.Unprotect

With Workbooks(NewBook).Sheets("RecordOfRounds")
Set DestCell = .Range("A53")
RngToCopy.Copy Destination:=DestCell
End With

Windows(OldBook).Activate

Workbooks(OldBook).Close SaveChanges:=False

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
*************************************************
 
Top