Hello Crlarus,
You weren't that far off actually.
I've taken it a little further and worked it so that you can use both codes for the same purpose. So, if you only have a meter number to work off, then using that number, you can still transfer all the required data just like the first code would do using the account number.
So, the two (similar) codes are:-
Sub FindDetails()
Dim lr As Long
Dim lr1 As Long
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim AccSearch As String
Set ws = Sheet1
Set ws1 = Sheet2
lr1 = ws1.Range("H" & Rows.Count).End(xlUp).Row
AccSearch = ws1.[B8].Value
Application.ScreenUpdating = False
ws1.Range("C9:G" & lr1).ClearContents
ws.Range("F1", ws.Range("F" & ws.Rows.Count).End(xlUp)).AutoFilter 1, AccSearch
lr = ws.Range("E" & Rows.Count).End(xlUp).Row
If lr > 1 Then
Union(ws.Range("A2:A" & lr), ws.Range("D2:E" & lr), ws.Range("G2:G" & lr), ws.Range("L2:L" & lr)).Copy
ws1.Range("C" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
End If
ws.[F1].AutoFilter
ws1.[J3:J4].ClearContents
ws1.[B8].Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
and
Sub FindDetails2()
Dim lr As Long
Dim lr1 As Long
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim MeterSearch As String
Set ws = Sheet1
Set ws1 = Sheet2
lr1 = ws1.Range("H" & Rows.Count).End(xlUp).Row
MeterSearch = ws1.[J3].Value
Application.ScreenUpdating = False
ws1.Range("C9:G" & lr1).ClearContents
ws.Range("G1", ws.Range("G" & ws.Rows.Count).End(xlUp)).AutoFilter 1, MeterSearch
lr = ws.Range("E" & Rows.Count).End(xlUp).Row
If lr > 1 Then
Union(ws.Range("A2:A" & lr), ws.Range("D2:E" & lr), ws.Range("G2:G" & lr), ws.Range("L2:L" & lr)).Copy
ws1.Range("C" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
ws.Range("F2:F" & lr).Copy ws1.[XFD1]
ws1.[J4] = ws1.[XFD1]
ws1.[B8] = ws1.[XFD1]
End If
ws.[G1].AutoFilter
ws1.[J4].Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
The code "FindDetails" is the same one as previous but now also clears the data from the destination sheet before new data is transferred across.
You should be able to pick up the minor additions.
The code "FindDetails2" is similar but searches for the meter number entered into J3 instead and then transfers the relevant data to the destination sheet as well. The relevant account number (if found) appears in J4 as well as appearing in B8.
BTW, don't use the same subroutine name for more than one code in the same project as an error will be thrown up.
Here is the link to the updated version:-
http://ge.tt/7orqzCm2
Play around with it and see if it works for you.
Test it in a copy of your work book first.
I hope that this helps.
Cheerio,
vcoolio.
Firstly, thank you for your time and response. To answer your clarifying questions, the data will be sourced from non-contiguous columns and too many account numbers to legitimize a drop down select option (full set is about 19,000 Accounts and a total of 660,000 metric reads with corresponding dates). I have not explored the idea of a search bar, but for now, simply an input cell will suffice (I assume should an input not match with anything on the index sheet it would return nothing thus confirming human error at that point?).
Regarding the code I have been working with, I cannot figure where to start. My experience with VBA only provides insight towards its potential; I cannot find a suitable example to even attempt trial and error to any real constructive progress. Find loops and Copy and Paste loops can work, but trying to return the data to a page that has formulas is where I run into issues. A link to a set of dummy data with deliberate redundancies (not to be removed) and deliberate blank cells is below; I hope that is enough data for a sufficient example.
http://ge.tt/96aKAAm2
I will review the example you sent in the meantime.
Thanks so much,
crlarus