Specifying Row to Copy Across ?

[dim]

Hi,

Im wondering how to specify a row in a different workbook, using a number in
cell A1, to copy using VBA?

Something like?...:

Sheets("Sheet1").Select
n = (Range("A1").Value)
Workbooks.Open Filename:= _"C:\Book2.xls"
Rows(Cells(n, n).Value).Copy
Windows("Book1.xls").Activate
Sheets("Sheet2").Select
Rows("1:1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

 

[Joel]

With ThisWorkbook.Sheets("Sheet1")
n = .Range("A1").Value
End With

Workbooks.Open Filename:="C:\Book2.xls"
Set newbk = ActiveWorkbook
newbk.Rows(Cells(n, n).Value).Copy

With ThisWorkbook.Sheets("Sheet2").Rows("1:1")
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With

 

[dim]

Thanks Joel, I'll try that ASAP.

 

[dim]

Hi, unfortunately that didn't work...

At the "newbk.Rows(Cells(n, n).Value).Copy" line, I get a "Runtime Error 438
- Object doesn't support this property or method."

I had to change it slightly because I need my list item number to be
increased by 1. This is because the headings are in Row1 and not included in
the list. So therefore when the list shows '1' I want to copy row '2'. When
the list shows '2' I want to copy row '3' etc

Here is what I used:

With ThisWorkbook.Sheets("Sheet1")
n = .Range("A1").Value + 1
End With
Windows("3.xls").Activate
With ThisWorkbook.Sheets("Sheet1")
.Unprotect "1234"
End With
Set newbk = ActiveWorkbook
newbk.Rows(Cells(n, n).Value).Copy
With ThisWorkbook.Sheets("Sheet1")
.Protect "1234"
End With
Windows("73.xls").Activate
With ThisWorkbook.Sheets("DataIn").Rows("3:3")
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With

Any ideas?

Thanks.

 

[dim]

I tried changing it again to this but its still not working.

Now Im getting a Runtime Error 13 - Type Mismatch at the 9th line :
Rows(Cells(n, n).Value).Copy

Any ideas anyone?

Sheets("Sheet1").Select
n = Range("A1").Value
Windows("3.xls").Activate
With ThisWorkbook.Sheets("Sheet1")
.Unprotect "1234"
End With
Sheets("Sheet1").Select
n = n + 1
Rows(Cells(n, n).Value).Copy
With ThisWorkbook.Sheets("Sheet1")
.Protect "1234"
End With
Windows("73.xls").Activate
With ThisWorkbook.Sheets("DataIn").Rows("3:3")
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With

 

[dim]

In case anyone is wondering in future the code is:

n = Range("A1").Value

Rows(n).Copy