Copy and Paste in VBA using relative references? (Error Code 1004)

Question

New to this forum so sorry if this is off. I'm trying to do a simple copying of cell values from one worksheet in a book to another worksheet, but need to use relative cell references as the number of rows that will be copy/pasted changes depending on the data inputted.

The (very simple) code so far is:

Sub SuitorList()

'Defining Variables
Dim Row As Integer
Row = Sheets("References").Cells(6, 2).Value

'Copying Statistics
Sheets("Charts").Range(Cells(1, 1), Cells(Row, 1)).Value = _      
Sheets("Data").Range(Cells(1, 1), Cells(Row, 1)).Value

End Sub

This code works fine when I use absolute cell references (i.e. "B1:B7") but when I use a relative reference I receive error code 1004: Application-defined or object-defined error.

Any thoughts?

Might be you were referring to out of valid limit. We can analyse only if you share the exact code that getting error. — Jagadish Dabbiru, Aug 19 '14 at 15:09

Ravi Yenugu · Answer 1 · 2014-08-19T17:51:24.813

1

Alternative Solution:

If you are not a fan of Loops, use Worksheet.Cells Property

Sub SuitorList()    
'Defining Variables
Dim Row As Integer
Set wd = ThisWorkbook.Worksheets("Data")
Set wc = ThisWorkbook.Worksheets("Charts")
Row = Sheets("References").Cells(6, 2).Value

'Copying Statistics
Range(wd.Cells(1, 1), wd.Cells(Row, 1)).Copy Destination:=Range(wc.Cells(1, 1), wc.Cells(Row, 1))


End Sub

edited Aug 19 '14 at 17:51

answered Aug 19 '14 at 17:22

Ravi Yenugu

3,895
5
40
58

score 0 · Answer 2 · answered Aug 19 '14 at 14:56

If you are copying data from one sheet to another and the amount of data to be copied/pasted is always changing then I would do something like this. Which is filtering the data from your selection sheet then copying it and pasting it to your destination sheet by finding the first blank cell. You may have to mess with this a bit, but it is a good start.

'Defining Variables
Dim Row As Integer
Row = Sheets("References").Cells(6, 2).Value

'switches the sheet
Sheets("Charts").Select

'filters a table based on the value of the Row variable
ActiveSheet.ListObjects("Table1").range.AutoFilter Field:=1, Criteria1:= _
  range("Row"), Operator:=xlAnd

'moves to the first cell in the filtered range
range("A1").Select

'selects all values in the range and copies to clipboard
range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy

'switches the sheet back to data sheet
Sheets("Data").Select

'finds the first blank cell in the declared range you want to paste into
ActiveSheet.range("A:A").Find("").Select

'pastes the selection
ActiveSheet.Paste

score 0 · Accepted Answer · answered Aug 19 '14 at 15:46

Thanks for the help. I was able to find a work around using the following code:

Sub SuitorList()

'Defining Variables
Dim Row As Integer
Row = Sheets("References").Cells(6, 2).Value

'Copying Statistics
For i = 1 To Row
Sheets("Charts").Range("A" & i).Value = Sheets("Data").Range("A" & i).Value
Next


End Sub

Copy and Paste in VBA using relative references? (Error Code 1004)

3 Answers3

Alternative Solution: