Insert row

[Gizmo]

Excel 2003
I want to move data from one "InputSheet" to another "Calculation" sheet
that links to charts. I want to insert the data so that the chart ranges
expand, instead of having to set up the chart ranges ahead of time. Here's
the code i'm using now with the chart ranges set to 40 records.

Sub AddAshDataBtn()

'Update data on Process Runs Sheet based on new data entered on
AshDataEntry Sheet
ActiveWorkbook.Unprotect Password:="xxx"
Dim LDate As Date

Dim LAshDate As Date
Dim LName As String
Dim LComments As String
Dim LRPOT As Integer
Dim LP1C1WC As Integer
Dim LP1C2WC As Integer
Dim LP2C1WC As Integer
Dim LP2C2WC As Integer
Dim LP3C1WC As Integer
Dim LP3C2WC As Integer
Dim LP1C1POT As Integer
Dim LP1C2POT As Integer
Dim LP2C1POT As Integer
Dim LP2C2POT As Integer
Dim LP3C1POT As Integer
Dim LP3C2POT As Integer
ETC... (93 total Dims)

Dim LRow As Long
Dim LFound As Boolean

'Before adding new record, make sure a value was entered
If IsEmpty(Range("A4").Value) = False Then

'Retrieve new information
LAshDate = Range("A4").Value
LName = Range("C4").Value
LComments = Range("A36").Value
LRPOT = Range("E4").Value
LP1C1WC = Range("C9").Value
LP1C2WC = Range("C10").Value
LP2C1WC = Range("C11").Value
LP2C2WC = Range("C12").Value
LP3C1WC = Range("C13").Value
LP3C2WC = Range("C14").Value
LP1C1POT = Range("F9").Value
LP1C2POT = Range("F10").Value
LP2C1POT = Range("F11").Value
LP2C2POT = Range("F12").Value
LP3C1POT = Range("F13").Value
LP3C2POT = Range("F14").Value
ETC..


'Move to ProcessRuns Sheet to save the changes
Sheets("Process Runs").Visible = True
Sheets("Process Runs").Select
Sheets("Process Runs").Unprotect Password:="xxx"

LFound = False

LRow = 13

Do While LFound = False

'Encountered a blank record number (assuming end of list on
Records Sheet)
If IsEmpty(Range("AQ" & LRow).Value) = True Then
LFound = True
End If

LRow = LRow + 1
Loop

Range("AO" & LRow - 1).Value = LComments
Range("AP" & LRow - 1).Value = LRPOT
Range("AQ" & LRow - 1).Value = LAshDate
Range("AR" & LRow - 1).Value = LP1C1WC
Range("AS" & LRow - 1).Value = LP1C2WC
Range("AT" & LRow - 1).Value = LP2C1WC
Range("AU" & LRow - 1).Value = LP2C2WC
Range("AV" & LRow - 1).Value = LP3C1WC
Range("AW" & LRow - 1).Value = LP3C2WC
Range("AX" & LRow - 1).Value = LP1C1POT
Range("AY" & LRow - 1).Value = LP1C2POT
Range("AZ" & LRow - 1).Value = LP2C1POT
Range("BA" & LRow - 1).Value = LP2C2POT
Range("BB" & LRow - 1).Value = LP3C1POT
Range("BC" & LRow - 1).Value = LP3C2POT
ETC..



'Reposition back on AshDataEntry Sheet
Sheets("Process Runs").Protect Password:="xxx"
Sheets("Process Runs").Visible = False
Sheets("AshDataEntry").Select

'Clear entries from cells
Range("C4").Value = ""
Range("A36").Value = ""
Range("E4").Value = ""
Range("C9").Value = ""
Range("C10").Value = ""
Range("C11").Value = ""
Range("C12").Value = ""
Range("C13").Value = ""
Range("C14").Value = ""
Range("F9").Value = ""
Range("F10").Value = ""
Range("F11").Value = ""
Range("F12").Value = ""
Range("F13").Value = ""
Range("F14").Value = ""
ETC..

ActiveWorkbook.Save
MsgBox ("New Data was successfully added.")

ActiveWorkbook.Protect Password:="xxx"
End If

End Sub

 

[KC Rippstein hotmail com>]

Gizmo,
If you use Excel 2003's "List" functionality, then the range your are
pointing to will automatically expand as new data is added to the "list".

 

[Gizmo]

Thanks KC

I know how to set up the Named Ranges, but how would I Dim the Variables and
then refer to them in VBA?

Dim LName As ?
instead of
Dim LName As String

LName = Range("?").value
instead of
LName = Range("C4").Value

Range("?" & LRow - 1).Value = LName
instead of
Range("AO" & LRow - 1).Value = LName