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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,302
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
63,302
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,899
Messages
5,834,299
Members
430,275
Latest member
Halis

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