| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert row
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert row
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". -- Please remember to indicate when the post is answered so others can benefit from it later. "Gizmo" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert row
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 "KC Rippstein" wrote: 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". -- Please remember to indicate when the post is answered so others can benefit from it later. "Gizmo" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| insert row / insert column command buttons | Excel Worksheet Functions | |||
| How can I insert a date with an icon (calendar) insert | Excel Discussion (Misc queries) | |||
| Trying to insert a row. Insert wont Activate | Excel Worksheet Functions | |||
| Can I auto insert a worksheet when I insert a value in a cell. | Excel Worksheet Functions | |||
| Insert Next? Or insert a variable number of records...how? | Excel Discussion (Misc queries) | |||
Linear Mode
