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