Code Question?

Todd_M · Apr 23rd, 2002, 11:32 AM

Hi-

Worksheets("sheet3").Range("a1:h1") = ActiveCell([1, 8] to [1, 15])

How would you take info from from cells on sheet1.activecell.offset(1 ,8) plus each of the next 7 cells to the right (.offset(1, 15)) and paste it on sheet3.range("a1:h1")?

In addition, if range (a1:h1") on sheet3 has info in it's cells, then the next pasting of cells from sheet1 has to go to the next available row, where there is blank cells.

[a1].End(xlDown).Offset(1, 0).Select

Im assuming this statement has to be added into the code, however im not sure where?
Thanks-

Todd_M · Apr 23rd, 2002, 12:11 PM

Anyone?

Jay Petrulis · Apr 23rd, 2002, 01:42 PM

Hi,

How about the following?

'---------------
Sub test()
Dim lastrow As Long, rng As Range

lastrow = Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
With ActiveSheet
Set rng = ActiveCell.Offset(0, 7).Resize(,

End With
rng.Copy Sheets("Sheet3").Cells(lastrow + 1, 1)

End Sub
'--------------------

Not sure about the location and/or sizing of the range to be copied from this part:

ActiveCell.Offset(0, 7).Resize(,

so please amend as necessary.

Bye,
Jay

EDIT: I don't know why there is a smilie in there. The smilie character should be an 8. To see exactly, either view in 'edit' or 'reply with quote' mode.

[ This Message was edited by: Jay Petrulis on 2002-04-23 12:47 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 23rd, 2002, 11:32 AM	#1
Todd_M Board Regular Join Date: Feb 2002 Posts: 117	Hi- Worksheets("sheet3").Range("a1:h1") = ActiveCell([1, 8] to [1, 15]) How would you take info from from cells on sheet1.activecell.offset(1 ,8) plus each of the next 7 cells to the right (.offset(1, 15)) and paste it on sheet3.range("a1:h1")? In addition, if range (a1:h1") on sheet3 has info in it's cells, then the next pasting of cells from sheet1 has to go to the next available row, where there is blank cells. [a1].End(xlDown).Offset(1, 0).Select Im assuming this statement has to be added into the code, however im not sure where? Thanks-

Apr 23rd, 2002, 12:11 PM	#2
Todd_M Board Regular Join Date: Feb 2002 Posts: 117	Anyone?

Apr 23rd, 2002, 01:42 PM	#3
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Hi, How about the following? '--------------- Sub test() Dim lastrow As Long, rng As Range lastrow = Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row With ActiveSheet Set rng = ActiveCell.Offset(0, 7).Resize(, End With rng.Copy Sheets("Sheet3").Cells(lastrow + 1, 1) End Sub '-------------------- Not sure about the location and/or sizing of the range to be copied from this part: ActiveCell.Offset(0, 7).Resize(, so please amend as necessary. Bye, Jay EDIT: I don't know why there is a smilie in there. The smilie character should be an 8. To see exactly, either view in 'edit' or 'reply with quote' mode. [ This Message was edited by: Jay Petrulis on 2002-04-23 12:47 ]