Copy/paste error help
 

Hi,
I have this code that makes a search, then copies data from a particular
row, tehn pastes it into another workbook.

Sub print_mon_jobcard()
Dim i As String
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFirst As Range
Dim rngDestination As Range
Dim rngAllRecords As Range
Dim wks1 As Worksheet, wks2 As Worksheet

'On Error GoTo err_handler
i = InputBox("Please enter the job number you wish to print a job card for")
Set wks1 = ThisWorkbook.Worksheets("adhoc database")
Set wks2 = ThisWorkbook.Worksheets("Todays Calls")

On Error Resume Next
Set rngToSearch = wks1.Columns("b")
Set rngDestination = wks2.Cells(Rows.Count, "b").End(xlUp).Offset(1, 0)

Set rngFound = rngToSearch.Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If rngFound Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Else
On Error GoTo err_handler
Set rngFirst = rngFound
Set rngAllRecords = rngFound
Do
Set rngAllRecords = Union(rngAllRecords, rngFound)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
rngAllRecords.EntireRow.Copy rngDestination
wks3.PrintOut
End If
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub


However each time I run the code it will not paste the collected data and
comes back with this response:-

err 1004

The information cannot be pasted because the copy and the paste are are not
the same size & shape. Try one of the following

*Click a single cell, then paste
*Select a rectangle thats the same size and shape then paste.


I have made an exact copy of the worksheet where the data is collected from
to paste it into but still get this error.

Any help........??
many thanks

Copy/paste error help
 

Anthony,

have you found where the code fails, I think it is a range error whereby the
rngDestination is more that one cell.

rngAllRecords.EntireRow.Copy rngDestination

HTH Rob

"Anthony" wrote in message
...
Hi,
I have this code that makes a search, then copies data from a particular
row, tehn pastes it into another workbook.

Sub print_mon_jobcard()
Dim i As String
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFirst As Range
Dim rngDestination As Range
Dim rngAllRecords As Range
Dim wks1 As Worksheet, wks2 As Worksheet

'On Error GoTo err_handler
i = InputBox("Please enter the job number you wish to print a job card
for")
Set wks1 = ThisWorkbook.Worksheets("adhoc database")
Set wks2 = ThisWorkbook.Worksheets("Todays Calls")

On Error Resume Next
Set rngToSearch = wks1.Columns("b")
Set rngDestination = wks2.Cells(Rows.Count, "b").End(xlUp).Offset(1, 0)

Set rngFound = rngToSearch.Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If rngFound Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Else
On Error GoTo err_handler
Set rngFirst = rngFound
Set rngAllRecords = rngFound
Do
Set rngAllRecords = Union(rngAllRecords, rngFound)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
rngAllRecords.EntireRow.Copy rngDestination
wks3.PrintOut
End If
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub


However each time I run the code it will not paste the collected data and
comes back with this response:-

err 1004

The information cannot be pasted because the copy and the paste are are
not
the same size & shape. Try one of the following

*Click a single cell, then paste
*Select a rectangle thats the same size and shape then paste.


I have made an exact copy of the worksheet where the data is collected
from
to paste it into but still get this error.

Any help........??
many thanks

Copy/paste error help
 

maybe thats trur but how do i change it so that this problem is resolved?
thanks

"Rob" wrote:

Anthony,

have you found where the code fails, I think it is a range error whereby the
rngDestination is more that one cell.

rngAllRecords.EntireRow.Copy rngDestination

HTH Rob

"Anthony" wrote in message
...
Hi,
I have this code that makes a search, then copies data from a particular
row, tehn pastes it into another workbook.

Sub print_mon_jobcard()
Dim i As String
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFirst As Range
Dim rngDestination As Range
Dim rngAllRecords As Range
Dim wks1 As Worksheet, wks2 As Worksheet

'On Error GoTo err_handler
i = InputBox("Please enter the job number you wish to print a job card
for")
Set wks1 = ThisWorkbook.Worksheets("adhoc database")
Set wks2 = ThisWorkbook.Worksheets("Todays Calls")

On Error Resume Next
Set rngToSearch = wks1.Columns("b")
Set rngDestination = wks2.Cells(Rows.Count, "b").End(xlUp).Offset(1, 0)

Set rngFound = rngToSearch.Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If rngFound Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Else
On Error GoTo err_handler
Set rngFirst = rngFound
Set rngAllRecords = rngFound
Do
Set rngAllRecords = Union(rngAllRecords, rngFound)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
rngAllRecords.EntireRow.Copy rngDestination
wks3.PrintOut
End If
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub


However each time I run the code it will not paste the collected data and
comes back with this response:-

err 1004

The information cannot be pasted because the copy and the paste are are
not
the same size & shape. Try one of the following

*Click a single cell, then paste
*Select a rectangle thats the same size and shape then paste.


I have made an exact copy of the worksheet where the data is collected
from
to paste it into but still get this error.

Any help........??
many thanks

Copy/paste error help
 

changing this line seemed to work for me

rngAllRecords.EntireRow.Copy rngDestination.EntireRow

--


Gary


"Anthony" wrote in message
...
Hi,
I have this code that makes a search, then copies data from a particular
row, tehn pastes it into another workbook.

Sub print_mon_jobcard()
Dim i As String
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFirst As Range
Dim rngDestination As Range
Dim rngAllRecords As Range
Dim wks1 As Worksheet, wks2 As Worksheet

'On Error GoTo err_handler
i = InputBox("Please enter the job number you wish to print a job card
for")
Set wks1 = ThisWorkbook.Worksheets("adhoc database")
Set wks2 = ThisWorkbook.Worksheets("Todays Calls")

On Error Resume Next
Set rngToSearch = wks1.Columns("b")
Set rngDestination = wks2.Cells(Rows.Count, "b").End(xlUp).Offset(1, 0)

Set rngFound = rngToSearch.Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If rngFound Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Else
On Error GoTo err_handler
Set rngFirst = rngFound
Set rngAllRecords = rngFound
Do
Set rngAllRecords = Union(rngAllRecords, rngFound)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
rngAllRecords.EntireRow.Copy rngDestination
wks3.PrintOut
End If
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub


However each time I run the code it will not paste the collected data and
comes back with this response:-

err 1004

The information cannot be pasted because the copy and the paste are are
not
the same size & shape. Try one of the following

*Click a single cell, then paste
*Select a rectangle thats the same size and shape then paste.


I have made an exact copy of the worksheet where the data is collected
from
to paste it into but still get this error.

Any help........??
many thanks

Copy/paste error help
 

A reply to this question, which you started yesterday has already been
provided to the thread you started yesterday. It will copy range to range.


"Anthony" wrote:

Hi,
I have this code that makes a search, then copies data from a particular
row, tehn pastes it into another workbook.

Sub print_mon_jobcard()
Dim i As String
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFirst As Range
Dim rngDestination As Range
Dim rngAllRecords As Range
Dim wks1 As Worksheet, wks2 As Worksheet

'On Error GoTo err_handler
i = InputBox("Please enter the job number you wish to print a job card for")
Set wks1 = ThisWorkbook.Worksheets("adhoc database")
Set wks2 = ThisWorkbook.Worksheets("Todays Calls")

On Error Resume Next
Set rngToSearch = wks1.Columns("b")
Set rngDestination = wks2.Cells(Rows.Count, "b").End(xlUp).Offset(1, 0)

Set rngFound = rngToSearch.Find _
(What:=i, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If rngFound Is Nothing Then
MsgBox "No job with the number " & i & _
" has been found, please try again! "
Else
On Error GoTo err_handler
Set rngFirst = rngFound
Set rngAllRecords = rngFound
Do
Set rngAllRecords = Union(rngAllRecords, rngFound)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
rngAllRecords.EntireRow.Copy rngDestination
wks3.PrintOut
End If
Exit Sub

err_handler:
MsgBox Error, , "Err " & Err.Number
End Sub


However each time I run the code it will not paste the collected data and
comes back with this response:-

err 1004

The information cannot be pasted because the copy and the paste are are not
the same size & shape. Try one of the following

*Click a single cell, then paste
*Select a rectangle thats the same size and shape then paste.


I have made an exact copy of the worksheet where the data is collected from
to paste it into but still get this error.

Any help........??
many thanks