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 |
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 |
All times are GMT +1. The time now is 04:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com