Restoring a worksheet using a Macro

[thejacko5]

Hey All, I have another one.

I have an worksheet that will be used in conjunction with a database.

This output sheet will be used by many people who will augment it by deleting rows. However, because this sheet is linked with several macros, any change to the sheet structure will throw everthing off. People will be altering the sheet because it is linked to yet another sheet which summarizes the page data.

So if there is a way to create a macro that will restore the sheet to its original form, OR add a row at the bottom when one is deleted that would probably be the best solution. If row 6 is deleted then add a new row at 400 or something along those lines.

I have attached a sample of the output sheet. In a real world case it will be populated with data.


Muchos Gracias. Let me know if i can provide more information.

[thejacko5]

Attachment 6510


and the attachment..ibfool

[Bob Phillips]

Just hide row 493, and change H492 to H493 etc. and you should be fine.

[thejacko5]

Just hide row 493, and change H492 to H493 etc. and you should be fine.

Either I dont understand your solution because it is too simple or I didnt clearly explain my problems.

For example, this sheet is linked to a macro that will copy data into the "Selected Tags" section of the sheet, and the copying is designated to start at row 499.

If I were going through the data and deleted rows 6,7,8. The "Tags Selected" section has moved from 499 to 496, so when I run the macro the data will paste to a range that does not match.


I hope that helps.

[Bob Phillips]

There is no code in the sample, so I assumed the problem was with the formulae.

[thejacko5]

I know, there isnt any code on this page, the macros are on the input sheet. But I have to create something from scratch here. regardless. here is the macro from the database sheet.

[vba]Sub Button86_Click()
'Macro written 8/01 by JRF
'This macro sends the visible cells of the "Database" worksheet to the "Output" worksheet to produce a summary of the desired data.
'*EDIT THIS MACRO* - If you add tags to the database you must alter this macro.

Sheets("Output").Range("C4:O300").ClearContents
Sheets("Database").Range("A7:B200,Y7:AB200,AC7:AD200,AG7:AH200,AK7:AL200,AO 7:AO200").SpecialCells(xlCellTypeVisible).Copy Sheets("Output").Range("C4")
'Confirm the "Database" Ranges in the macro above correspond to those in the "Database" Worksheet

Sheets("Output").Range("C499:e513").ClearContents
With Sheets("Database")
myarray2 = Array(3, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 5)
'If adding a tag add 3,4, or 5 to the above string depending on what segment it applies to, 3 for FS, 4 for EDU, 5 for I&M, follow the same logic for removing a tag
For i = 3 To 24
'Example: If adding a tag increase the "For" Range (above) from "3 to 24", to, "3 to 25"
If FilterOn(.Cells(7, i)) Then
Sheets("Output").Cells(496 + i, myarray2(i - 3)).Value = .Cells(6, i).Value
End If
Next i
End With
End Sub
'DO NOT TOUCH THIS CODE
Function FilterOn(rngcell As Range) As Boolean
On Error GoTo err_handle
With rngcell.Parent.AutoFilter
With .Filters.Item(rngcell.Column - .Range.Column + 1)
FilterOn = .On
End With
End With

clean_up:
Exit Function

err_handle:
FilterOn = False
Resume clean_up
End Function[/vba]


EDIT: those notes are for the benefit of the next guy. I am leaving the company in a few days.

[Bob Phillips]

Well, it is still difficult to see what you are doing, thus difficult to be precise, but you shuld be using named ranges, and then you would not be hardcoding rows or columns.

[thejacko5]

Attachment 6512

There is the complete template that may help you see what is going on.

Can you elaborate on named ranges? example perhaps?

EDIT:

How about something along the lines of IF Rows ("4:495") are deleted then insert an equal amount of rows at 495. Something like that is what I am looking for if there is a way to make the macro fire when a row has been deleted.

[Bob Phillips]

Sure.

For instance you have some code aimed at Sheets("Output").Range("C499:e513")

If you create an Excel name for that range (Ctrl-F3), you could use

Sheets("Output").Range("myData1")

in your code. If the range shifts for any reason, your code will still work as Excel will maintain the reference.

[thejacko5]

understood, i just named a range however it said that the reference was $C$499:$E$513, will it still move properly even tho the references appear to be hard? ie: $

[Bob Phillips]

Delete a row before 499 and see what happens to it.

[thejacko5]

when i delete a row the macro worked but the data still shifted down one cell

[Bob Phillips]

I meant delete it and see how it affects the named range. Then you have to incorporate into your code.

[thejacko5]

ok, with the named range, it moves when rows are deleted, now that I have incorporated it into my code, the macro no longer works, this is what I did

[vba]Sub Button86_Click()
'Macro written 8/01 by JRF
'This macro sends the visible cells of the "Database" worksheet to the "Output" worksheet to produce a summary of the desired data.
'*EDIT THIS MACRO* - If you add tags to the database you must alter this macro.

Sheets("Output").Range("C4:O300").ClearContents
Sheets("Database").Range("A7:B200,Y7:AB200,AC7:AD200,AG7:AH200,AK7:AL200,AO 7:AO200").SpecialCells(xlCellTypeVisible).Copy Sheets("Output").Range("C4")
'Confirm the "Database" Ranges in the macro above correspond to those in the "Database" Worksheet

Sheets("Output").Range("Tags_Area").ClearContents
With Sheets("Database")
myarray2 = Array(3, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 5)
'If adding a tag add 3,4, or 5 to the above string depending on what segment it applies to, 3 for FS, 4 for EDU, 5 for I&M, follow the same logic for removing a tag
For i = 3 To 24
'Example: If adding a tag increase the "For" Range (above) from "3 to 24", to, "3 to 25"
If FilterOn(.Cells(7, i)) Then
Sheets("Output").Cells(Tags_Area + i, myarray2(i - 3)).Value = .Cells(6, i).Value
End If
Next i
End With
End Sub[/vba]

that highlighted red used to be "496" is there something else i have to change?

[Bob Phillips]

I assume that Tags_Area refers to C499:E513?

If so, the second one should be

[vba]

Sheets("Output").Cells(Sheets("Output").Range("Tags_Area").Row - 3 + i, myarray2(i - 3)).Value = .Cells(6, i).Value
[/vba]

[thejacko5]

awesome. I just have one more question regarding this code.


If you go into the document and sort the "education" or information & media columns the output does not align with the top of the "Tags Area" as does with "Financial Services" in column C

If you try running the macro you will see that the first cells designated by "4" in the array copy much further down the page.


Is there a way to make that so. All three columns should begin to copy in the same row.


Did i mention you are a god?