Hello Everyone,
Below is the macro code that I use to populate our cash sheet for balancing. I press the button and it copies the information from the “Upload sheet” and pastes the information into various other sheets as specified. We do this every day and have to manually change the Range(“B13”).Select portion every day so that it pastes the information into the correct row. This only works because the range is on the same line for all the worksheets.
I’d like to update the macro (or create another macro) to do 2 things before it copies and pastes the information.
First, I’d like the button to trigger an input box that asks the user to enter a date. Once the date is entered, I’d like the macro to look at column A in the worksheet called “Lead Sheet”, find the date entered by the user as well as the corresponding row number to that date.
From there, I’d like my macro to go in and update my populate macro to find all the Range(“B13”) and update to the correct row number. Once that macro has been run, then I’d like my populate macro that copies and pastes the information to run.
For example, on the “Lead Sheet” February 20th is in row 22, February 21 is in row 23 etc…. When the input box pops up and the user enters in 02/20/2017 I want the macro to go the lead sheet find 02/20/2017, find the row number (22) and then update the populate macro’s range for that row number. So instead of B13, the macro says B22. Once that has been done I’d like the populate macro to run as usual.
Below is the macro code that I use to populate our cash sheet for balancing. I press the button and it copies the information from the “Upload sheet” and pastes the information into various other sheets as specified. We do this every day and have to manually change the Range(“B13”).Select portion every day so that it pastes the information into the correct row. This only works because the range is on the same line for all the worksheets.
I’d like to update the macro (or create another macro) to do 2 things before it copies and pastes the information.
First, I’d like the button to trigger an input box that asks the user to enter a date. Once the date is entered, I’d like the macro to look at column A in the worksheet called “Lead Sheet”, find the date entered by the user as well as the corresponding row number to that date.
From there, I’d like my macro to go in and update my populate macro to find all the Range(“B13”) and update to the correct row number. Once that macro has been run, then I’d like my populate macro that copies and pastes the information to run.
For example, on the “Lead Sheet” February 20th is in row 22, February 21 is in row 23 etc…. When the input box pops up and the user enters in 02/20/2017 I want the macro to go the lead sheet find 02/20/2017, find the row number (22) and then update the populate macro’s range for that row number. So instead of B13, the macro says B22. Once that has been done I’d like the populate macro to run as usual.
Code:
Sub Populate()
' Populate Macro
Sheets("Upload Sheet").Select 'NET AMOUNT AMEX
Range("D2:AX2").Select
Selection.Copy
Sheets("AmEx WME").Select
Range("b13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Upload Sheet").Select 'ACH
Range("D6:AD6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("ACHs").Select
Range("b13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Upload Sheet").Select 'FNB LEAD SHEET
Range("D21:G21").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Lead Sheet - FNB").Select
Range("b13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Upload Sheet").Select
Range("A15").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("A16").Select
'
End Sub
[Code]
I was able to write the following code to trigger the input box when the button is pressed:
[Code]
Sub CashDate()
Dim CashDate As Date
CashDate = InputBox("Enter date for which you are balancing cash. This is usually the prior bank business day.")
Range("C25").Value = CashDate
End Sub
[Code]
And I know that to trigger different macros I can use the Call function, but I don’t know how to write the macro to find the date, row and update my other macro or how to tell it to do all of it in the correct order.
Oof… I hope all of this makes sense! I’m a macro and VBA novice code writing novice, but I’m decent at reading and understanding code, so I think I can edit a macro with the help of google if I have the basic framework. Just something to keep in mind for anyone who responds. I recognize this may not be the most efficient way to do it either so I am open to other possible solutions. Any help will be much appreciated! :)