Populating different sheets based on the value in a column on another sheet.

[Chris Slater]

Dear All,
I'm trying to create a workbook that splits out data to different sheets based on values in a specific column in a main "Data" sheet.
I have one sheet in my workbook which has multiple rows of data relating to POS Card Terminal Receipts. There are 2 Terminals, each with their own ID, and the rows have a 'Terminal ID' column that I wish to use to create data on 2 additional sheets - 1 for each Terminal.
I need to be able to interrogate the main data sheet and automatically create rows in each on the new sheets based on the value in the 'Terminal ID' column.
So on the sheet for Terminal 1, it should look down the data - checking the 'Terminal ID' column for the value '1' and then, for each case of when it finds it, create a new row on the Sheet for Terminal 1.
The sheets will already be set up but I want the addition of new lines to be dynamic based on new values in the "Data" sheet. Every few days I download a 'csv' file with new transactions in it. I then simply copy and paste the new ones into the top rows of the "Data" sheet on the spreadsheet.
At present I have to go through a process of filtering by Terminal ID and then copying and pasting the resulting data into the relevant Terminal sheet. This is a little bit long winded but manageable however, the plan is to add more terminals over time which will start becoming a huge pain.
I would like to be able to just paste over the range in the "Data" sheet with the latest data and have each Terminal sheet auto update without having the faff of 'filtering, copying and pasting' the data for each Terminal separately.
The scenario is then further complicated by the fact that I then want have Sub-Totals on each of the "Terminal" sheets. In the attached sample, however, I have removed the "Sub-Totalling" in order to simplify things but I would need to be able to turn it back on at the end.
I did consider recording a Macro to go through the process of "Filtering, Copying and Pasting" but I'm sure there must be a better way.

Any help gratefully received.
Thanks in advance.

FYI: The Terminal Sheets are "06321792" and "06524883" which relate to the values in "Column D" on the Data Sheet.

Sheet 1: Data

Sheet 2: 06321792

Sheet 3: 06524883

 

[DanteAmor]

Hi Chris:

I did consider recording a Macro to go through the process of "Filtering, Copying and Pasting" but I'm sure there must be a better way.

In fact that is a good option.


You left out a couple of details.
a) The records are going to be added to the end of the data that already exists in the destination sheet or the destination sheet must always start in row 1.
b) Where do the subtotals go?

Try the following macro, always create the sheets, no matter if you have 2 IDs or multiple IDs. And I put the subtotal for column K, it is the one I saw that has values.

The macro has the principle of filter, copy and paste.

Sub create_worksheets()
  Dim c As Range, sh As Worksheet, ky As Variant
  Dim lr As Long
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
 
  Set sh = Sheets("Data") 'fit the name of your sheet
 
  With CreateObject("Scripting.Dictionary")
    For Each c In sh.Range("D2", sh.Range("D" & Rows.Count).End(xlUp))
      If c.Value <> "" Then .Item(c.Value) = Empty
    Next c
   
    For Each ky In .Keys
      sh.Range("A1").AutoFilter 4, ky
      On Error Resume Next: Sheets(ky).Delete: On Error GoTo 0
      Sheets.Add(, Sheets(Sheets.Count)).Name = ky
      sh.AutoFilter.Range.EntireRow.Copy Range("A1")
      lr = Range("D" & Rows.Count).End(3).Row
      Range("K" & lr + 1).Formula = "=SUM(K2:K" & lr & ")"
    Next ky
  End With
 
  sh.Select
  sh.ShowAllData
End Sub

----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --

 

[Chris Slater]

Hi Dante,
Apologies but I've been away from my office for a while. Thanks for taking the time to reply and put that VBA routine together for me.
It's great apart from 1 small issue - which i guess is my own fault for not putting enough detail in my original request. I have a column in the two pages relating to the terminals which I fill out manually when I reconcile the accounts. It's called "Reconciled" and it's 'Column L' on the above images. When I have checked and reconciled each row of the Statement I put a "Yes" in that column and it helps me ensure that I don't miss anything. The VBA routine seems to overwrite everything so I would lose my record of what's been completed.
I don't know if it's even possible to do this easily but I kind of resigning myself to having to do this the hard way.
Thanks very much for your help though.

 

[DanteAmor]

The VBA routine seems to overwrite everything so I would lose my record of what's been completed.

That is one of my doubts, where do I put the information, at the end of the existing records?

When I have checked and reconciled each row of the Statement I put a "Yes"

???

I don't know if it's even possible to do this easily

Could you explain what you need?
Which records to copy, where to paste, where the subtotals go.
Give examples again of all, including subtotals. How are sheets 2 and 3 before the macro and how should sheets 2 and 3 be after the macro.

To give the examples, use the XL2BB tool.