Sub FindDetails() Dim lr As Long Dim ws As Worksheet Dim ws1 As Worksheet Dim ASearch As String Set ws = Sheet1 Set ws1 = Sheet2 AccSearch = ws1.[B8].Value Application.ScreenUpdating = False 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 Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
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
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
Member requests are more likely to be responded to.
Members can monitor the statuses of their requests from their account pages.
A CCM membership gives you access to additional options.
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