Create a Macro to Update Another Macro Based on User Input box

[mkf001]

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 20^th 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.

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! :)

 

[Norie]

Why don't you just ask the user for the date at the start of this sub, find the date, put the row in a variable and then use that variable in the rest of the code?

Where are the dates located?

 

[mkf001]

Wow! Really appreciate the quick response!

The dates are located on each sheet that the macro needs to populate, but it only needs to examine one sheet to find the date and row number since all the sheets are set up the same way. I'm open to your suggestion, but I'll need you to explain further how to do what you've described. Everything I know about macros and VBA has been self taught via Google and trial and error.

 

[Norie]

Where would the date(s) be found?

Is it always column B?

Which sheet would be 'best' to use to look for the date?

Without those details I can't really post any code for finding the date but let's say we've done that bit and we now have a variable, eg DateRow, which contains the row where the date was found then the original code could be rewritten along these lines.

Sub Populate()
' Populate Macro
Dim DateRow As Long

    ' code to find date

    DateRow = 22    ' dummy value for demonstration
    
    'NET AMOUNT AMEX
    Sheets("Upload Sheet").Range("D2:AX2").Copy
    Sheets("AmEx WME").Range("B" & DateRow).PasteSpecial Paste:=xlPasteValues
    
    ' ACH
    Sheets("Upload Sheet").Range("D6:AD6").Copy
    Sheets("ACHs").Range("B" & DateRow).PasteSpecial Paste:=xlPasteValues
    
    'FNB LEAD SHEET
    Sheets("Upload Sheet").Range("D21:G21").Copy
    Sheets("Lead Sheet - FNB").Range("B" & DateRow).PasteSpecial Paste:=xlPasteValues

End Sub

 

[mkf001]

The dates will always be found in column A of the "Lead Sheet". Column B is the first column where information is pasted in.