Crating Ranges help

[Ifatooki]

Hi
Does anyone now why the following doesnt work? or help me with my
logic I have a worksheet that summarizes a number of Pivot Tables.
There are 30 different Cost Centers(CC) that i want to grab the sheet
and put into one sheet. So knowing that my summary sheet is A1:Q50 i
can crate a Range for each of the CC and Copy and PasteSpecial, SO HOW
DO I GET THE RANGE TO WORK? HELP PLEASSSSS

Dim CC_Range As Range
Dim CC_Range_Source As Range
Dim CC_Range_Dest As Range
Dim cc_Cell As Variant
Dim RangeStart As String
Dim RangeEnd As String
Dim Rangestring As String
Dim Rangestring2 As String

Dim cc_dest_cell As Variant
Set CC_Range = Worksheets("Pivot").Range("CC_List")
Set CC_Range_Source = Worksheets("Template").Range("CostCenter")
RangeStart = 1
RangeEnd = 50

For Each cc_Cell In CC_Range

'1.Causes an application Error ???

Rangestring = Chr(34) & "$A$" & RangeStart & ":$Q$" & RangeEnd &
Chr(34)
Set CC_Range_Dest = Worksheets("Sheet1").Range(Rangestring)

'2. Causes Type MisMatch can a string be converted to a range

Rangestring2 = "Worksheets(" & Chr(34) & "Sheet1" & Chr(34) &
").Range(" & Rangestring & ")"
Set CC_Range_Dest = Rangestring2

CC_Range_Source.Copy
CC_Range_Dest.PasteSpecial
Application.CutCopyMode = False
RangeStart = RangeStart + 52
RangeEnd = RangeEnd + 53
Next cc_Cell
End Function

Ray (e-mail address removed)

 

[Dave Peterson]

You don't need to add the double quotes:

Rangestring = Chr(34) & "$A$" & RangeStart & ":$Q$" & RangeEnd & Chr(34)
becomes:
Rangestring = "$A$" & RangeStart & ":$Q$" & RangeEnd

In fact, since it's a string, you could drop the $ signs.
Rangestring = "A" & RangeStart & ":Q" & RangeEnd

Then you could use:

Set CC_Range_Dest = Worksheets("Sheet1").Range(Rangestring)

 

[Ifatooki]

Hi
Does anyone now why the following doesnt work? or help me with my
logic I have a worksheet that summarizes a number of Pivot Tables.
There are 30 different Cost Centers(CC) that i want to grab the sheet
and put into one sheet. So knowing that my summary sheet is A1:Q50 i
can crate a Range for each of the CC and Copy and PasteSpecial, SO HOW
DO I GET THE RANGE TO WORK? HELP PLEASSSSS

Dim CC_Range As Range
Dim CC_Range_Source As Range
Dim CC_Range_Dest As Range
Dim cc_Cell As Variant
Dim RangeStart As String
Dim RangeEnd As String
Dim Rangestring As String
Dim Rangestring2 As String

Dim cc_dest_cell As Variant
Set CC_Range = Worksheets("Pivot").Range("CC_List")
Set CC_Range_Source = Worksheets("Template").Range("CostCenter")
RangeStart = 1
RangeEnd = 50

For Each cc_Cell In CC_Range

'1.Causes an application Error ???

Rangestring = Chr(34) & "$A$" & RangeStart & ":$Q$" & RangeEnd &
Chr(34)
Set CC_Range_Dest = Worksheets("Sheet1").Range(Rangestring)

'2. Causes Type MisMatch can a string be converted to a range

Rangestring2 = "Worksheets(" & Chr(34) & "Sheet1" & Chr(34) &
").Range(" & Rangestring & ")"
Set CC_Range_Dest = Rangestring2

CC_Range_Source.Copy
CC_Range_Dest.PasteSpecial
Application.CutCopyMode = False
RangeStart = RangeStart + 52
RangeEnd = RangeEnd + 53
Next cc_Cell
End Function

Ray (e-mail address removed)

thanks I thought i had already tried that