Macro to expand formulas to additional rows
 

I'm working with a large multi-sheet workbook and need to expand the formulas
on many sheets from Row 2 to a variable number of rows. Can someone offer
vba code that will allow me to prompt for the variable and then expand the
formulas to the specific number of rows accordingly?

I'm trying to keep my spreadsheet lean and don't want to have 750 rows of
formulas sitting there mostly unused. Sometimes, I'll only have 5 rows of
data and
other times 750.

My knowledge of vba is second grade so any documentation in the code itself
indicating what's going on is most encouraging. Thanks!

Macro to expand formulas to additional rows
 

Hi

An example he

Public Sub RedesignTables()

' removing passwords
Sheets("MySheet1").Unprotect Password:="*****"
Sheets("MySheet2").Unprotect Password:="*****"
....
' redesigning MySheet1
Worksheets("MySheet1").Activate
Worksheets("MySheet1").Range("A2").Sort _
Key1:=Worksheets("MySheet1").Range("A2"), Order1:=xlAscending, _
Header:=xlGuess
varEntries = [MySheet1_Entries]
varRows = [MySheet1_Rows]
varPrepared = [MySheet1_Prepared]
n = varRows - varEntries - varPrepared
' checking the number of rows, adding/removing them
If n 0 Then
' remove abundant prepared rows
Worksheets("MySheet1").Rows((varEntries + 2) & ":" & (varEntries + n
+ 2)).Delete Shift:=xlUp
ElseIf n < 0 Then
' add missing prepared rows
Do Until n = 0
Worksheets("MySheet1").Rows((varEntries + 2) & ":" & (varEntries
+ 2)).Insert Shift:=xlDown
Worksheets("MySheet1").Range("A" & (varEntries + 3) & ":D" &
(varEntries + 3)).Copy
Worksheets("MySheet1").Range("A" & (varEntries + 2) & ":D" &
(varEntries + 2)).PasteSpecial Paste:=xlFormulas
n = n + 1
Loop
End If
Worksheets("MySheet1").Range("A2").Select

' redesigning MySheet2
Worksheets("MySheet2").Activate
...
' restore passwords
Sheets("MySheet1").Protect Password:="*****", UserInterfaceOnly:=True
Sheets("MySheet1").EnableAutoFilter = True
Sheets("MySheet2").Protect Password:="*****", UserInterfaceOnly:=True
Sheets("MySheet2").EnableAutoFilter = True
...
End Sub

The named ranges defined in workbook, and used by procedu
MySheet1_Entries=COUNTA(Articles)
Articles=OFFSET(MySheet1!$A$2;;;COUNTIF(MySheet1!$ A:$A;"<")-1;1)
MySheet1_Rows=COUNTIF(MySheet1!$D:$D;"<")-1
MySheet1_Prepared=SetUp!$B$11

NB! - the number of prepared empty rows for every sheet is stored on sheet
SetUp. The column D on MySheet1 contains formulas (others may contain too -
I used this column to determine the total number of rows (filled+prepared)
in table.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Sharon P" wrote in message
...
I'm working with a large multi-sheet workbook and need to expand the
formulas
on many sheets from Row 2 to a variable number of rows. Can someone offer
vba code that will allow me to prompt for the variable and then expand the
formulas to the specific number of rows accordingly?

I'm trying to keep my spreadsheet lean and don't want to have 750 rows of
formulas sitting there mostly unused. Sometimes, I'll only have 5 rows of
data and
other times 750.

My knowledge of vba is second grade so any documentation in the code
itself
indicating what's going on is most encouraging. Thanks!