compiling worksheets in excel

[TommyJ]

This might be the wrong place to ask this so maybe i'm just asking for a direction to go in. I need to update a worksheet in excel from other worksheets in the same book.

basically, worksheet one should be all the rows from worksheets 2 through 5 which are updated once in a while. i think i can program the control button that runs the update from excel myself.

[Bob Hansen]

Don't need Macro Scheduler to do that, so this is probably not the correct forum. But since you are here, try this:

It's been years since I have done that, but you should be able to use Excel to roll up the cells in the other sheets to a top level sheet. Formula in spreadsheet1, cell A5 will look something like this:
=Sum(worksheet2:+A5,worksheet3:+A5,worksheet4:+A5,worksheet5:+A5)

After that formula is done correctly, just copy it to the other cells. Be sure to get the correct Relative and Absolute symbols before you copy to multiple cells.

Sorry if my syntax is not correct, but this should get you pointed in the correct direction.

[TommyJ]

thanks, i actually found this bit of vba that does almost exactly what i want. The problem is that i don't want it to overwrite the last column on the "merged" worksheet. How can i leave that last column static?

Sub WSMerge()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Application.ScreenUpdating = False
Set DestSh = Worksheets("Merged")
Worksheets("Merged").UsedRange.Offset(4, 0).ClearContents
For Each sh In Sheets(Array("Sheet2","Sheet3"))
Last = LastRow(DestSh)
sh.UsedRange.Offset(4, 0).Copy DestSh.Cells(Last + 1, 1)
Next
Application.ScreenUpdating = True
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

Thanks for your help though