Anthony's VBA Forum

[jmccolley]

I need help with some code for an excel spreadsheet that I plan to convert to HTML for our company website. I have created a workbook of our rates and discount levels with 4 worksheets (Plan A, Plan B, Plan C, Plan D) and need to be able to update Plan A so that it automatically updates sheets B, C, & D. I need it to keep the formatting and all the formulas I have in place.
With the code I have currently written, I have equipment listed by category and when I add a new piece of equipment in a category it adds the new row but then shifts the last item in the category down to the next category heading and deletets that category heading.

This is what I have:

Private Sub Worksheet_Activate()
Dim MastSht, DestSht As Worksheet
Dim last As Long
Set MastSht = Sheets("Plan A")
Set DestSht = Sheets("Plan B")
Application.ScreenUpdating = False
last = MastSht.Cells(Rows.Count, "F").End(xlUp).Row
DestSht.Range("A1:A" & last).Value = MastSht.Range("A1:A" & last).Value
DestSht.Range("B1:D" & last).Value = MastSht.Range("F1:H" & last).Value
DestSht.Range("C1:D" & last).Value = MastSht.Range("G1:H" & last).Value
DestSht.Range("D1:D" & last).Value = MastSht.Range("H1:H" & last).Value
Application.ScreenUpdating = True
End Sub

[mohan.ankur]

I think where you get the last row, may you should try...

....end(xlup).row+1

Regards
Ankur