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,D 84,H84,I84,C81,D81,H81,I81,C82,D82,H82,I82,C83,D83 ,H83,I83,C85,D85,H85,I85,c16,d16,h16,i16,D85,I55,c 94,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!

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"

"Jas" wrote:

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,D 84,H84,I84,C81,D81,H81,I81,C82,D82,H82,I82,C83,D83 ,H83,I83,C85,D85,H85,I85,c16,d16,h16,i16,D85,I55,c 94,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!

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


"Joel" wrote:

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"

"Jas" wrote:

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,D 84,H84,I84,C81,D81,H81,I81,C82,D82,H82,I82,C83,D83 ,H83,I83,C85,D85,H85,I85,c16,d16,h16,i16,D85,I55,c 94,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!

It would be the same as your original code

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

"Jas" wrote:

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


"Joel" wrote:

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"

"Jas" wrote:

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,D 84,H84,I84,C81,D81,H81,I81,C82,D82,H82,I82,C83,D83 ,H83,I83,C85,D85,H85,I85,c16,d16,h16,i16,D85,I55,c 94,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!

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

"Joel" wrote:

It would be the same as your original code

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

"Jas" wrote:

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


"Joel" wrote:

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"

"Jas" wrote:

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,D 84,H84,I84,C81,D81,H81,I81,C82,D82,H82,I82,C83,D83 ,H83,I83,C85,D85,H85,I85,c16,d16,h16,i16,D85,I55,c 94,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!