Copy from one workbook and paste in another with VBA

davidkhbgh

New Member
Joined
Jan 2, 2006
Messages
40
I am trying to copy a range from one workbook to a range in another workbook:


Code:
    'print pallet tag
    If ProductionData.CheckBox1.Value = True Then
        ActiveCell.Offset(0, 5) = "Skid Complete"
        
        Dim endCell As Range
        Set endCell = ActiveCell.Offset(0, 2)
        
        Dim sngSkidNumber As Single
        sngSkidNumber = Range("E8").Value
        Workbooks("Tally Sheet ver7.2.xls").Sheets("Pallet Tag").Range("B3").Value = sngSkidNumber
        'Range("B3").Value = sngSkidNumber
        ActiveWorkbook.Sheets("ProductionRecord").Activate
        
        'find top of range
        Dim os As Integer
        For x = endCell.Row - 1 To 11 Step -1
            os = endCell.Row - x
            If endCell.Offset(-os, 3).Text = "Skid Complete" Or x = 11 Then
                Dim startCell As Range
                Set startCell = endCell.Offset(-os + 1, -2)
                Exit For
            End If
        Next x
        
        'Debug.Print "Start: " & startCell.Column & ":" & startCell.Row
        'Debug.Print "End: " & endCell.Column & ":" & endCell.Row
            
        Range(Cells(startCell.Row, startCell.Column), Cells(endCell.Row, endCell.Column)).Select
        Selection.Copy
        Workbooks("Tally Sheet ver7.2.xls").Sheets("Pallet Tag").Range("C3").Selection.Paste
        Workbooks("Tally Sheet ver7.2.xls").Sheets("Pallet Tag").PrintOut Copies:=1
        Workbooks("Tally Sheet ver7.2.xls").Sheets("Pallet Tag").Selection.ClearContents
        
        Sheets("ProductionRecord").Select
    End If

a message tells me this object does not support this method:

Code:
Workbooks("Tally Sheet ver7.2.xls").Sheets("Pallet Tag").Range("C3").Selection.Paste

Any help would be appriciated.

Dave
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Watch MrExcel Video

Forum statistics

Threads
1,114,406
Messages
5,547,759
Members
410,811
Latest member
adustin42
Top