Find last cell in column and paste copied contents

djpeter

Board Regular
Joined
Jun 10, 2008
Messages
166
I am curious how I can get the following code to work. I keep getting an error at the line specified. I also have instructions as to what I am trying to do at the end. Thank you

Sub add_accounts()
'declaring variables
Dim i As Integer
Dim x As Integer
'selects the sheet
Sheets("Print_By_Model").Select
'clears the contents
Range("A8:A120").ClearContents
'adds the objects
x = Cells(2, 1)
Name = Cells(4, 1)

Dim c, a As Integer

'grabs the sheet to copy from
Sheets("Actual").Select

For i = 2 To 400
If Cells(i, 4) = Name Then
Cells(i, 1).Copy

'grabs the sheet to paste to
Sheets("Print_By_Model").Select
'this is the line i get a debug message on, i want it to paste into row 8 and so on...
Worksheets("Print_By_Model").Range("A8").End (xlDown)
Selection.Paste

End If
Next i
End Sub


' have two columns of data in one sheet,
'i want another sheet to look in that sheet in a column that matches a certain cell in the
'first sheet, then i want it to copy another column in the same row if it does match and
'paste it in the other sheet.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Does this work as expected?

Code:
Sub add_accounts()
Dim i As Integer, x As Integer, Name As Variant
With Sheets("Print_By_Model")
    .Range("A8:A120").ClearContents
    x = .Cells(2, 1).Value
    Name = .Cells(4, 1).Value
End With
With Sheets("Actual")
    For i = 2 To 400
        If .Cells(i, 4).Value = Name Then
            .Cells(i, 1).Copy Destination:=Sheets("Print_By_Model").Range("A8").End(xlDown).Offset(1)
        End If
    Next i
End With
End Sub
 
Upvote 0
It looks as though it would do it correctly except can it start pasting in the 8th row??
 
Upvote 0
Try

Code:
Sub add_accounts()
Dim i As Integer, x As Integer, Name As Variant
x = 7
With Sheets("Print_By_Model")
    .Range("A8:A120").ClearContents
    x = .Cells(2, 1).Value
    Name = .Cells(4, 1).Value
End With
With Sheets("Actual")
    For i = 2 To 400
        If .Cells(i, 4).Value = Name Then
            x = x + 1
            .Cells(i, 1).Copy Destination:=Sheets("Print_By_Model").Range("A" & x)
        End If
    Next i
End With
End Sub
 
Upvote 0
I guess it doesn't matter what cell i start on because it will look for the first empty row right? Also, it has to be pasted as value...
 
Upvote 0
Then try

Code:
Sub add_accounts()
Dim i As Integer, Name As Variant
With Sheets("Print_By_Model")
    .Range("A8:A120").ClearContents
    x = .Cells(2, 1).Value
    Name = .Cells(4, 1).Value
End With
With Sheets("Actual")
    For i = 2 To 400
        If .Cells(i, 4).Value = Name Then
            .Cells(i, 1).Copy
            Sheets("Print_By_Model").Range("A8").End(xlDown).Offset(1).PasteSpecial Paste:=xlPasteValues
        End If
    Next i
End With
End Sub
 
Upvote 0
I keep getting the following error on this line
Application defined or object defined error....

Sheets("Print_By_Model").Range("A8").End(xlDown).Offset(1).PasteSpecial paste:=xlPasteValues
 
Upvote 0
Try

Code:
Sub add_accounts()
Dim i As Integer, Name As Variant
With Sheets("Print_By_Model")
    .Range("A8:A120").ClearContents
    x = .Cells(2, 1).Value
    Name = .Cells(4, 1).Value
End With
With Sheets("Actual")
    For i = 2 To 400
        If .Cells(i, 4).Value = Name Then
            .Cells(i, 1).Copy
            Sheets("Print_By_Model").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        End If
    Next i
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top