Copying several columns

[achidsey]

Excel Experts,

I want to copy several columns of numbers from one spreadsheet to another.
I want to save each column of numbers as a separate variable and then go to
the other sheet, and paste each of them. I'd prefer not to copy and paste
the columns one by one because of the extra code and time to switch back and
forth between the sheets.

My spreadsheets and code are similar to the following

FIRST SHEET SECOND SHEET
A B A B C

1 Price1 Price2
2 10.25 50.50
3 20.25 60.50
4 30.25 70.50

Sub CopyNumbers()

Set rngA = Range("A2:A4")
Set rngB = Range("B2:B4")

Sheets("Second").Activate

Cells.Find(What:="Price1").Offset(1).Select
Selection.Value = rngA.Value

Cells.Find(What:="Price2").Offset(1).Select
Selection.Value = rngB.Value

End Sub


The problem with this code is that only the first value in the range is
copied to the new spreadsheet. For example, under Price1, only 10.25 is
entered. I recognize that if I increased the size of the selection in the
targe sheet to 3 cells, all three values would be entered. However, the
column of numbers is of a different size each time so I don't know how large
to make the selection.

Do I have to select the correct size range in the target sheet for this to
work, or is there another way to make this work?

Thanks,
Alan

 

[Tom Ogilvy]

Sub CopyNumbers()

Set rngA = Range(Range("A2"),Range("A2").End(xldown))
Set rngB = rngA.Offset(0,1)

Sheets("Second").Activate

Cells.Find(What:="Price1").Offset(1).Select
Selection.Resize(rngA.rows.count,1).Value = rngA.Value

Cells.Find(What:="Price2").Offset(1).Select
Selection.Resize(rngB.rows.count,1).Value = rngB.Value

End Sub

 

[achidsey]

Tom,

Great. A big help.

Thanks,
Alan

--
achidsey

Sub CopyNumbers()

Set rngA = Range(Range("A2"),Range("A2").End(xldown))
Set rngB = rngA.Offset(0,1)

Sheets("Second").Activate

Cells.Find(What:="Price1").Offset(1).Select
Selection.Resize(rngA.rows.count,1).Value = rngA.Value

Cells.Find(What:="Price2").Offset(1).Select
Selection.Resize(rngB.rows.count,1).Value = rngB.Value

End Sub