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.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

djpeter

Board Regular
Joined
Jun 10, 2008
Messages
166
It looks as though it would do it correctly except can it start pasting in the 8th row??
 

djpeter

Board Regular
Joined
Jun 10, 2008
Messages
166
and how would i get it to paste as values instead of just paste?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

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
 

djpeter

Board Regular
Joined
Jun 10, 2008
Messages
166
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...
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

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
 

djpeter

Board Regular
Joined
Jun 10, 2008
Messages
166
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
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,088
Messages
5,599,664
Members
414,325
Latest member
kfg1287

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
Top