Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying Row to Copy Across ?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying Row to Copy Across ?
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying Row to Copy Across ?
Thanks Joel, I'll try that ASAP.
"Joel" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying Row to Copy Across ?
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. "Joel" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying Row to Copy Across ?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and paste versus copy and insert copied cells | New Users to Excel | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
copy formulas from a contiguous range to a safe place and copy them back later | Excel Programming | |||
EXCEL FILE a copy/a copy/a copy ....filename | New Users to Excel | |||
Code to copy range vs Copy Entire Worksheet - can't figure it out | Excel Programming |