Thanks Dave. Your stuff you post here is really good. In fact, I am getting
to ready to run another of your macros. But I will post the question that I
have for that macro in another thread. You're the bomb. Really appreciate
it.
--
Nothing in life is ever easy - just get used to that fact.


"Dave Peterson" wrote:

And you can use the data in column F to determine the last row?

Dim NextRow as long
with worksheets("Somesheethere")
nextrow = .cells(.rows.count,"F").end(xlup).row + 1
.cells(nextrow, "F").value = "Totals"
.cells(nextrow, "H").formular1c1 = "=sum(r4c:r[-1]c)"
end with

Using the .formular1c1 is a very nice way to address that range.

r4c is row 4 of the same column
r[-1]c is one row up (from the totals row) and the same column



KennyD wrote:

Thank you to all of you for responding so quickly. It's funny that you
responded to this post, Dave, because I have been modifying a macro that you
wrote 2 years ago on this board. :)

The actual way I got it to work was this (as subroutine in part of a larger
macro):

For Each Sh In Basebook.Worksheets
If Sh.Name < Newsh.Name And Sh.Visible Then
ColNum = 2
RwNum = RwNum + 1
'Create a link to the sheet in the B column
Newsh.Cells(RwNum, 2).Formula =
"=HYPERLINK(""#""&CELL(""address"",'" & Sh.Name & "'!A1)," _
& """" & Sh.Name & """)"
Newsh.Cells(RwNum, 2).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
For Each myCell In Sh.Range("C3,T14,T15") '<--Change the range
ColNum = ColNum + 2
Newsh.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!" &
myCell.Address(False, False)
Newsh.Cells(RwNum, ColNum).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Next myCell

But now I need to figure out how to add the word "Totals" in 1 row after the
last row on Column F and then sum the values in Column H from H4:H-LastRow
--
Nothing in life is ever easy - just get used to that fact.

"Dave Peterson" wrote:

Dim LastRow as long

With activesheet
'change the A to whatever column you can use to find the last row
lastrow = .cells(.rows.count,"A").end(xlup).row

with .range("b2,b4:B" & lastrow _
& ",d2,d4:d" & lastrow _
& ",f2,f4:f" & lastrow _
& ",h2,h4:h" & lastrow).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With

end with

(You don't need to select the range to work with it.)


KennyD wrote:

Need help changing the range on this range so that it will go to the last
row. For example, instead of going from B2,B4:B18, I need it to go from
B2,B4:"the last row that has any information in it". Same with column D,
Column F and Column H. So I imagine something like B2,B4:B&LastRow. But
don't know how to make that happen. Any help would be greatly appreciated.

Range("B2,B4:B18,D2,D4:D18,F2,F4:F18,H2,H4:H18").S elect
Range("H4").Activate
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
--
Nothing in life is ever easy - just get used to that fact.

--

Dave Peterson
.


--

Dave Peterson
.