Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Crating Ranges help
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Crating Ranges help
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 wrote: 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Crating Ranges help
On May 3, 2:43 pm, Ifatooki wrote:
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 thanks I thought i had already tried that |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Ranges | Excel Worksheet Functions | |||
Finding external Excel files and crating a dashboard. | Excel Discussion (Misc queries) | |||
Help crating a formula with various arguments | Excel Worksheet Functions | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
using ranges | Excel Worksheet Functions |