Macro paste values help

[mooseo]

This is my first substantial excel macro... so far, things are going
well, but I'm confused about pasting values only...

I've been using this expression to cut and paste, which works fine.
CopySheet.Range(Hs).Copy Destination:= PasteSheet.Cells(BottomRow, 1)
but then realized that I need to paste the values only.

I tried using
CopySheet.Range(Hs).Value = PasteSheet.Cells(BottomRow, 1).Value
which doesn't work, presumably because I'm just giving it the starting
cell, rather than a range.

I guess that I can use separate Copy and PasteSpecial instructions, but
from everything I've read on this group, I should try to avoid this.
I'm not sure how to go about giving it a paste range, though, because
this will change each time through a loop.

I've put the full code below, if it will help. I'd certainly appreciate
any other suggestions that anyone notices.

Thanks,
mike



Sub ShuffleForceDataPrism()
'
' ShuffleForceDataPrism Macro
' Macro recorded 1/24/2007 by Moose
' This macro combines the force and wave height
' data from all sites into a single sheet
' in the format suitable for Prism software
'

' turn calculation off to speed up... don't forget to turn it back on
Application.Calculation = xlCalculationManual


' Define Variables
Dim i As Integer

Dim PasteSheet As Worksheet
Dim DataSheet As Worksheet

Dim HeaderText As Variant
Dim SheetsList As Variant

Dim BottomRow As Integer

' Set variables
Set PasteSheet = Worksheets("ADP")
Set CopySheet = Worksheets("A2")
Hs = "Q2:Q75"
F1cm = "O2:O75"


SheetsList = Array("A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8",
"A9", "A10", "A11", "A12", "A13", "B1", "B2", "B3", "B4", "B5", "B6",
"B7", "B8", "B9", "B10", "B11", "B12", "B13", "C1", "C2", "C3", "C4",
"C5", "C6", "C7", "C8", "C9", "C10", "C11", "C12", "C13", "C14", "C15",
"C16", "C17", "C18", "C19", "C20", "C21")
HeaderText = Array("Hs", "A01", "A02", "A03", "A04", "A05", "A06",
"A07", "A08", "A09", "A10", "A11", "A12", "A13", "B01", "B02", "B03",
"B04", "B05", "B06", "B07", "B08", "B09", "B10", "B11", "B12", "B13",
"C01", "C02", "C03", "C04", "C05", "C06", "C07", "C08", "C09", "C10",
"C11", "C12", "C13", "C14", "C15", "C16", "C17", "C18", "C19", "C20",
"C21")

' Clear PasteSheet
PasteSheet.Cells.ClearContents

' Add Header Row using the text in Header Text
For i = 1 To 47 'Loop through text array
PasteSheet.Cells(1, i).Value = HeaderText(i - 1)
Next

' Loop through sheets list and cut data
BottomRow = 2
For i = 0 To 2
Set CopySheet = Worksheets(SheetsList(i))
CopySheet.Range(Hs).Value = PasteSheet.Cells(BottomRow, 1) 'get Hs and
put in Col 1
CopySheet.Range(F1cm).Copy Destination:=PasteSheet.Cells(BottomRow, i +
2) 'Get force and put in correct Col
BottomRow = BottomRow + 50 ' increment a lot, will remove empty rows
later

Next

 

[Bernie Deitrick]

Mike,

You just had it backwards - as long as your ranges are the same size:

PasteSheet.Cells(BottomRow, 1).Value = CopySheet.Range(Hs).Value

HTH,
Bernie
MS Excel MVP

 

[mooseo]

Ah, of course. That explains all the blank ranges in my worksheet...

Thanks a bunch.

moose

 

[mooseo]

Actually, though, I realize that this isn't quite what I need. The
problem is that I don't necessarily know how long the range is going to
be. BottomRow is the row index of the first cell where I want to paste
the new values. Can I easily specify to go from BottomRow to BottomRow
+ Range(Hs).Rows.Count ?

I guess it might be easier to just copy and paste special. but it seems
so much cleaner to not do that.

Thanks again,
moose

 

[Bernie Deitrick]

Moose,

PasteSheet.Cells(BottomRow, 1).Resize(Range(Hs).Rows.Count).Value = CopySheet.Range(Hs).Value

should work...

HTH,
Bernie
MS Excel MVP

 

[mooseo]

Fantastic, thanks!