Trouble pasting

  • Thread starter Thread starter Sandy
  • Start date Start date
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
 
..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
 
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
 
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
 
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
 
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
 
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...
 
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.
 
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...
 
Hi Barb,

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error 9 5
Workbook_BeforeClose 3
Select and copy certain tabs using macro 7
CSV code works on some, but not all machines 3
assign sheet for embedded macro to run in 4
A simple if <> nn then Msgbox "No Match" 5
If Product already exist then overwrite that row 4
copy COLUMN from 1 worksheet to another (in a different workbook) 3

Back
Top