Summary Sheet from range specified in a worksheet

[Guest]

Hi,

I have used Ron de Bruins code for creating a summary spreadsheet but I need
to modify it so that it refers to a range that is specified in a worksheet
rather than in the macro. The reason for this is that fields may need to be
added or removed, depending on user requirements and I need to give them that
flexibility (also they wont mess with the code that way either).

Does anyone know how I can move that part of the code to look at a
spreadsheet values instead? The code is:


'The links to the first sheet will start in row 2
RwNum = 1

For Each Sh In Basebook.Worksheets
If Sh.Name <> Newsh1.Name And Sh.Visible And Sh.Name Like ("DT - *")
Then
ColNum = 1
RwNum = RwNum + 1
'Copy the sheet name in the A column
Newsh1.Cells(RwNum, 1).Value = Sh.Name

For Each myCell In
Sh.Range("B1,C79,D79,H79,I79,C80,D80,H80,I80,C84,D84,H84,I84,C81,D81,H81,I81,C82,D82,H82,I82,C83,D83,H83,I83,C85,D85,H85,I85,c16,d16,h16,i16,D85,I55,c94,D94,H94,I94,d55,h97,h98,h99,h100,h101,h102") '<--Change the Range
ColNum = ColNum + 1
Newsh1.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!"
& myCell.Address(False, False)
Next myCell

End If
Next Sh

Thanks in advance!

 

[Guest]

I think I understnad your question. The data in the RANGE(Text String) is a
text string.

Sh.Range("B1,C79,D79,H79,I79,C80,D80,H80,I80," & _
"C84,D84,H84,I84,C81,D81,H81,I81,C82," & _
"D82,H82,I82,C83,D83,H83,I83,C85,D85," & _
"H85,I85,c16,d16,h16,i16,D85,I55,c94," & _
"D94,H94,I94,d55,h97,h98,h99,h100,h101,h102")

You could also do the same using a worksheet
Sh.Range(range("A1").value)

where A1 contains the string
"B1,C79,D79,H79,I79,C80,D80,H80,I80," & _
"C84,D84,H84,I84,C81,D81,H81,I81,C82," & _
"D82,H82,I82,C83,D83,H83,I83,C85,D85," & _
"H85,I85,c16,d16,h16,i16,D85,I55,c94," & _
"D94,H94,I94,d55,h97,h98,h99,h100,h101,h102"

 

[Guest]

Thanks Joel. Is there a way to have these values in A1:10 and loop it through
those values?

 

[Guest]

It would be the same as your original code

for each cell in Sh.Range(range("A1").value)
'enter your code here
next cell

 

[Guest]

Thanks Joel. What I ended up doing is setting another variable (myrng) as the
range that I needed to refer to ("A1:A100") and ran the rest of the code
normally. Quite simple when someone else tells you the answer!! Thanks once
again.

Jas