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