-1

I have an excel file in where i have records and i want to filter those records based on a specific column criteria. And those records should be replicated to multiple other sheets based on the criteria. And as the data gets updated in the master sheet so the data should also get updated in the respective criteria sheets as well.

I have tried my hands in excel using "Get Data" => "From Other Sources" => "From Microsoft Query". Below i have attached the images. But as the data points are increasing its not able to perform properly. Please do help if any other way is possible with the help of MACROs or VBAs. As far as i know the above thing works similar to a SQL query and using a WHERE clause.

Below i have attached the images of where the criteria column is "Location" and for sample i have created sheets based on location

enter image description hereenter image description hereenter image description here

Below is the error message:

enter image description here

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Spandan Rout
  • 79
  • 3
  • 9

2 Answers2

1

Here is a code I created recently to have approx. this result:

Private Sub CommandButton1_Click()
Dim i As Integer
Dim wn As String, col As String, col1 As Integer
Worksheets("Master").Columns("A:A").Replace What:="", Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Worksheets("mod").Visible = True
Worksheets("Master").Rows(1).Copy
Worksheets("mod").Activate
Worksheets("mod").Cells(1, 1).Select
ActiveSheet.Paste
Worksheets("Master").Select
wn = InputBox("Criteria", "Criteria")
col = InputBox("Specify column", "Main sheet")
col1 = Columns(col).Column
lastrow = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
With ThisWorkbook
.Worksheets("mod").Copy after:=.Sheets(.Sheets.Count)
ActiveSheet.Name = wn
End With
For i = 2 To lastrow
If Worksheets("Master").Cells(i, col1) Like "*" & wn & "*" Then
Worksheets("Master").Rows(i).Copy
Worksheets(wn).Activate
erow = Worksheets(wn).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Worksheets(wn).Cells(erow, 1).Select
ActiveSheet.Paste
Worksheets(wn).Columns("A:ZZ").AutoFit
Worksheets("Master").Activate
End If
Next i
Application.CutCopyMode = False
Worksheets("mod").Visible = False
Worksheets("Master").Columns("A:A").Replace What:=" ", Replacement:="",LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
On Error Resume Next
End Sub

Hope it will help and waiting for your feedback! Feel free to contact me, if further info needed !

Boni
  • 52
  • 4
  • hi @Boni, Thanks a lot!! Can we connect over, actually i quite literally have never worked with VBAs. So, if you could please help with how to process the above code that would save my life!! – Spandan Rout Aug 19 '20 at 03:32
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Aug 20 '20 at 09:40
1

@Spandan Rout Hi, simply create a button in first sheet and copy this code in VBA window Create a worksheet named "mod" (blank worksheet with no data) Be sure that Worksheet "master" is also available ... that's it ! for "Criteria" use Name of the location (Boston,New Delhi,...) for "Specify Column" type in column (A,B,C,...), where criteria is in (D in this case)

Boni
  • 52
  • 4
  • I tried your solution. But i am stuck due to some issue. So once the code runs there is a type box for the criteria I have mentioned and when I fill in the criteria it gives me another box to fill in "Main Sheet" and then it gives me an error of type mismatch So just wanted to confirm what do we need to enter in these boxes. – Spandan Rout Aug 26 '20 at 14:01
  • @Spandan Rout Hello, indeed first type box is for Criteria where you introduce the name of the location (Boston,...) and in second type box you should input the column ref., where criteria is in your Main sheet (A,B,C,D,...) ... so if criteria= "Boston" in main sheet in column B, then first input= Boston and second input = B ... Regards – Boni Aug 27 '20 at 15:24
  • just further query, when running the code I am getting an error “Overflow” and only the first row is getting pasted to the new Criteria sheet (just the headings) and not the data. Can you please help with this. And thanks a lot for all your responses!! – Spandan Rout Aug 31 '20 at 15:59
  • @Spandan Rout: try to replace Integer by Long in Dim – Boni Sep 01 '20 at 09:15