Hello all. I am working on a project where I will need to pull data from multiple sheets and place it on a common sheet for
(I am attaching an example)
I have an input box that asks for a date.
Based on what date is entered, the following data should be copied from the account worksheets and placed on the "Recon"
worksheet: the account number ("C2") and the balance (Column D).
For instance, if the user entered 09/12/2007,
the account number and the balance in the D Column for the last entry for that date would be copied to the "Recon"
I have prepared some code that will prefill the account number and then display the input box which
Dim recondate As String
Dim anyWS As Worksheet
Const dSheet = "Recon"
copyToRow = 5 'initialize
For Each anyWS In Worksheets
Select Case anyWS.Name
Case "Main", "Recon"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'For Each Worksheet In anyWS
Set rngToCopy = anyWS.Range("C2")
Worksheets(dSheet).Range("A" & copyToRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
'Worksheets(dSheet).Range("A" & copyToRow).PasteSpecial Paste:=xl.PasteFormats
copyToRow = copyToRow + rngToCopy.Rows.Count
Application.CutCopyMode = False
recondate = Application.InputBox("Enter Date to Reconcile." & vbNewLine _
& vbNewLine _
& "Format should be MM/DD/YYYY", "Reconciliation Date", vbYesCancel)
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My question is: How do I search through all the worksheets using the date returned from the input box, locate the
balance (in Column D) in the row corresponding to the last entry of the date (since there may be multiple entries for that
date per the attached example) and copy that to the "Recon" worksheet?
Thank you in advance for any
advice/assistance...and I apologize if I have not explained this properly!