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

tags24

New Member
Joined
Aug 11, 2010
Messages
2
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

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

Perhaps it is this one:
Code:
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

New Member
Joined
Aug 11, 2010
Messages
2
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

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