Hi Everyone,
So I am attempting to accomplish some hopefully simple VBA in Excel. First let me list the structure of my Excel file and then what I need the VBA to do:
Excel file structure:
- Sheet1 is named FORMULA_OUTPUT and this has all my formulas on it which take values from Sheet2 named ENTER_VALUES_HERE and format all the data
- Sheet2 as mentioned is named ENTER_VALUES_HERE and its where the business people enter they values
- Sheet3 is named LISA_INPUT which after the VBA runs, will hopefully take the values from the FORMULA_OUTPUT tab, where each cell that has a formula on the FORMULA tab is copied, and then pasted in the LISA_INPUT tab, but pasted "special" as TEXT only
My Requirements:
1. Busjness user enters data in ENTER_VALUES_HERE tab which works fine now
2. FORMULA_OUTPUT tab automatically takes values from the ENTER tab and runs formulas on them to reformat the data and such - also works fine now
**This takes me to my first need
3. Some VBA code at the workbook level which does the following:
a. when a user enters a new line of data on the ENTER_VALUES_HERE tab, and then clicks on SAVE, the VBA code will go to the FORMULA_OUTPUT tab, and copy ROW 1 which has all the formulas in it, and paste it to the exact same row the user entered the business data on within the ENTER_VALUES_HERE tab. This way the FORMULAS tab can then run its formulas on the new row
**My only other need is the following
4. Some additional VBA code which works in conjunction with the above code, and when a user selects SAVE, will first do the above copy and paste, but will then also copy the entire row that was just entered on the FORMULAS_OUTPUT tab, navigate to the LISA_INPUT tab, and do a paste "special" on the exact same row once again, but paste it as TEXT. This way on that LISA_INPUT tab we are only seeing text values, no formulas or weird formatting.
Here is the VBA code I have come up with thus far to do the second step (copy from FORMULAS to LISA_INPUT), but I am no expert and Excel is telling me its wrong
I really appreciate anyones help. I've always gotten alot of good answers from this forum, lots of good people, so thank you in advance!!!!
__________________________________________________________________________________________________________________
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'this event handler will copy the contents of SourceSheet to TargetSheet when a user selects the SAVE option
Dim TargetSheet As Worksheet.LISA_INPUT 'target sheet where the new values will be copied
Dim SourceSheet As Worksheet.FORMULA_OUTPUT 'source sheet where the new values will be copied from
'Step1: activate the FORMULA_OUTPUT sheet (SourceSheet) and select cell A2 since that is where the copy will start from
'Users will be entering business data on the ENTER_VALUES_HERE tab
Workbook.Sheets(SourceSheet).Activate
Range("A2").Select
'Step2: clear contents of Clipboard to remove any memory issues
Application.CutCopyMode = False
'Step3: calculate the range of cells to copy by identifying all cells that have a value in them on SourceSheet and then copy
TBD how to select all cells that have a formula in them alone???
'Step4: Select appropriate cells to execute the COPY from SourceSheet
Range(TBD).Select
Selection.Copy
'Step5: activate the TargetSheet (LISA_INPUT) sheet and select cell A2 for the PASTE to start
Workbook.Sheets(TargetSheet).Activate
Range("A2").Select
'Step6: Paste the new contents using paste special to so we only paste the cell values as TEXT
ActiveSheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Step7: clearn Excel memory and clipboard
Application.CutCopyMode = False
Set SourceSheet = Nothing
Set TargetSheet = Nothing
End Sub
So I am attempting to accomplish some hopefully simple VBA in Excel. First let me list the structure of my Excel file and then what I need the VBA to do:
Excel file structure:
- Sheet1 is named FORMULA_OUTPUT and this has all my formulas on it which take values from Sheet2 named ENTER_VALUES_HERE and format all the data
- Sheet2 as mentioned is named ENTER_VALUES_HERE and its where the business people enter they values
- Sheet3 is named LISA_INPUT which after the VBA runs, will hopefully take the values from the FORMULA_OUTPUT tab, where each cell that has a formula on the FORMULA tab is copied, and then pasted in the LISA_INPUT tab, but pasted "special" as TEXT only
My Requirements:
1. Busjness user enters data in ENTER_VALUES_HERE tab which works fine now
2. FORMULA_OUTPUT tab automatically takes values from the ENTER tab and runs formulas on them to reformat the data and such - also works fine now
**This takes me to my first need
3. Some VBA code at the workbook level which does the following:
a. when a user enters a new line of data on the ENTER_VALUES_HERE tab, and then clicks on SAVE, the VBA code will go to the FORMULA_OUTPUT tab, and copy ROW 1 which has all the formulas in it, and paste it to the exact same row the user entered the business data on within the ENTER_VALUES_HERE tab. This way the FORMULAS tab can then run its formulas on the new row
**My only other need is the following
4. Some additional VBA code which works in conjunction with the above code, and when a user selects SAVE, will first do the above copy and paste, but will then also copy the entire row that was just entered on the FORMULAS_OUTPUT tab, navigate to the LISA_INPUT tab, and do a paste "special" on the exact same row once again, but paste it as TEXT. This way on that LISA_INPUT tab we are only seeing text values, no formulas or weird formatting.
Here is the VBA code I have come up with thus far to do the second step (copy from FORMULAS to LISA_INPUT), but I am no expert and Excel is telling me its wrong
I really appreciate anyones help. I've always gotten alot of good answers from this forum, lots of good people, so thank you in advance!!!!
__________________________________________________________________________________________________________________
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'this event handler will copy the contents of SourceSheet to TargetSheet when a user selects the SAVE option
Dim TargetSheet As Worksheet.LISA_INPUT 'target sheet where the new values will be copied
Dim SourceSheet As Worksheet.FORMULA_OUTPUT 'source sheet where the new values will be copied from
'Step1: activate the FORMULA_OUTPUT sheet (SourceSheet) and select cell A2 since that is where the copy will start from
'Users will be entering business data on the ENTER_VALUES_HERE tab
Workbook.Sheets(SourceSheet).Activate
Range("A2").Select
'Step2: clear contents of Clipboard to remove any memory issues
Application.CutCopyMode = False
'Step3: calculate the range of cells to copy by identifying all cells that have a value in them on SourceSheet and then copy
TBD how to select all cells that have a formula in them alone???
'Step4: Select appropriate cells to execute the COPY from SourceSheet
Range(TBD).Select
Selection.Copy
'Step5: activate the TargetSheet (LISA_INPUT) sheet and select cell A2 for the PASTE to start
Workbook.Sheets(TargetSheet).Activate
Range("A2").Select
'Step6: Paste the new contents using paste special to so we only paste the cell values as TEXT
ActiveSheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Step7: clearn Excel memory and clipboard
Application.CutCopyMode = False
Set SourceSheet = Nothing
Set TargetSheet = Nothing
End Sub
