Specifying Row to Copy Across ?

D

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
 
J

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
 
D

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.
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top