Copying columns of numbers 2

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

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

 

[bpeltzer]

You can copy a range, select a single cell and paste. But program it like an
Excel copy/paste, rather than reading and writing the values (easiest way to
get the gist is to record a similar operation and look at the resulting VB
code). Here's a mod to your posted sub:
Sub CopyPasteNumbers()

Dim SoureSheet As String
sourcesheet = ActiveSheet.Name

Range("A2:A4").Copy
Sheets("Second").Activate
Cells.Find(What:="Price1").Offset(1).Select
ActiveSheet.Paste

Sheets(sourcesheet).Activate
Range("B2:B4").Copy
Sheets("Second").Activate
Cells.Find(What:="Price2").Offset(1).Select
ActiveSheet.Paste

End Sub

HTH. --Bruce