Copying a row of data from one workbook 1 to a new row of workbook 2

[tags24]

Hi everyone,
I'm a notice when it comes to programming. I've been trying to write a macro to have a fixed row in my first file copy to the next open row of my second file. The problem is everytime I run this macro I get a run time error subscript out of range and I don't know why.

'create variable
'strFirstFile = "X:\Resolution\Blue Roof\Emergancy Service Request Form.xls"
'strSecondFile = "X:\Resolution\Blue Roof\Emergancy Service Data gathering form.xls"
'Set wbk = Workbooks("X:\Resolution\Blue Roof\Emergancy Service Request Form.xls")
' Windows(wbk).Activate
' With wbk.Sheets("Sheet3")
' Range("A2:AQ2").Copy
'End With
'Set wbk = Workbooks("X:\Resolution\Blue Roof\Emergancy Service Data gathering form.xls")
' With wbk.Sheets("Sheet1")
' lMaxRows = Cells(Rows.Count, "B").End(xlUp).Row
' Range("B" & lMaxRows + 1).Select
' Range("A").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
' False, Transpose:=False
'End With
End Sub

 

[Joe4]

Welcome to the Board!

Which row does it highlight if you click Debug? It should indicate the offending row.

Perhaps it is this one:

Range("A").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Looks like you are missing your row number in your Range reference (I don't think "Range(A)" will work.)

 

[tags24]

Thanks for the quick reply. I'm haven't gotten the the error as long as both workbooks are open however the data isn't copying correctly. Can you take a look at my code and tell me why?

strFirstFile = "Emergancy Service Request Form.xls"
strSecondFile = "Emergancy Service Data gathering form.xls"
Set wbk = Workbooks("Emergancy Service Request Form.xls")
With wbk.Sheets("Sheet3")
Range("A2:AQ2").Copy
End With
Set wbk = Workbooks("Emergancy Service Data gathering form.xls")
With wbk.Sheets("Sheet1")
n = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Cells(n, "A").Resize(, 4).Value = _
Cells(1, "A").Resize(, 4).Value
MsgBox "Data has been updated !!", vbExclamation + vbInformation, "Travelers"

 

[Joe4]

Can you explain, in plain English, how you are determining the location and size of the range you are trying to copy?

I am not use I follow what you are trying to do by trying to read your code, especially towards the end of it.