Adding a variable into a range declaration

[Malvaro]

Here is my defined variable:

Dim RowCount As Long, myRange As Range
Set myRange = Columns("A:A")
RowCount = Application.WorksheetFunction.CountA(myRange)

I am trying to apply the RowCount variable in two situations:

a) copying a formula down the entire length of column B to last row:

Range("B8").Select
ActiveCell.FormulaR1C1 = "The details of my formula"
Range("B8").Select
Selection.Copy
Range("B9:B1000").Select
ActiveSheet.Paste

I'm trying to replace B1000 with a reference to the variable RowCount.
I've tried... Range("B9:B(RowCount)").Select ... and it doesn't work.

b) creating a new pivot table:

Range("A7:BB1000").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:
_
"'Raw Data'!R7C1:R1000C54").CreatePivotTabl
TableDestination:="", TableName _
:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

I'm trying to replace "A7:BB1000" and "R7C1:R1000C54" with their V
counter-part that references the variable RowCount.

Somehow I'm thinking that I'm looking right past the obvious.

Please help me figure this out..

 

[Dave Peterson]

a)

Range("B8:b" & rowcount).formula = "The details of my formula"

b)

Range("A7:BB" & rowcount).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Raw Data'!R7C1:R" & rowcount & "C54").CreatePivotTable
TableDestination:="", TableName _
:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

 

[Malvaro]

Thanks for all your help!!!

a)

Range("B8:b" & rowcount).formula = "The details of my formula"

b)

Range("A7:BB" & rowcount).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Raw Data'!R7C1:R" & rowcount & "C54").CreatePivotTable
TableDestination:="", TableName _
:="PivotTable1", DefaultVersion:=xlPivotTableVersion10