Copying Rows from one table to another based on conditions

[DanielMachin]

Hi Guys,

I currently have a main data table as below

[TABLE="width: 500"]
<tbody>[TR]
[TD]WEEK[/TD]
[TD]TIMESTAMP[/TD]
[TD]DOCKET[/TD]
[TD]DATE[/TD]
[TD]SOURCE[/TD]
[TD]LITRES[/TD]
[TD]WEEKEND[/TD]
[TD]UNITS[/TD]
[TD]REGO[/TD]
[TD]DRIVER[/TD]
[TD]EMAIL[/TD]
[TD]SEALS[/TD]
[TD]TANKS[/TD]
[TD]AMOUNT[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]151526267[/TD]
[TD]DD-2179[/TD]
[TD]01/02/2018[/TD]
[TD]Mittagong[/TD]
[TD]40000[/TD]
[TD]YES[/TD]
[TD]245[/TD]
[TD]CBD-098[/TD]
[TD]Daniel[/TD]
[TD]blabla[/TD]
[TD]262727[/TD]
[TD]12[/TD]
[TD]$400[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]26262727[/TD]
[TD]DD-2180[/TD]
[TD]01/02/2018[/TD]
[TD]Gosford[/TD]
[TD]40000[/TD]
[TD]NO[/TD]
[TD]356[/TD]
[TD]WOS-789[/TD]
[TD]Daniel[/TD]
[TD]shjsjs[/TD]
[TD]258546[/TD]
[TD]12,13[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]68768769[/TD]
[TD]DD-2181[/TD]
[TD]02/02/2018[/TD]
[TD]Mittagong[/TD]
[TD]40000[/TD]
[TD]NO[/TD]
[TD]245[/TD]
[TD]CBD-098[/TD]
[TD]Daniel[/TD]
[TD]sjdjdkj[/TD]
[TD]465466[/TD]
[TD]14[/TD]
[TD]$400[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]237272[/TD]
[TD]DD-2182[/TD]
[TD]03/02/2018[/TD]
[TD]Woy Woy[/TD]
[TD]40000[/TD]
[TD]NO[/TD]
[TD]356[/TD]
[TD]WOS-789[/TD]
[TD]Daniel[/TD]
[TD]sgsh[/TD]
[TD]456554[/TD]
[TD]14[/TD]
[TD]$350[/TD]
[/TR]
</tbody>[/TABLE]

I have 2 other sheets both of which are invoice sheets.

The only difference is one of them charges for dockets from Mittagong and the other from Gosford and Woy Woy.

Every week i Manually filter the main table by week number then Source and manually copy only the selected data into a smaller
table in the invoice sheets.

The smaller tables look like below



INVOICE FOR GOSFORD AND WOY WOY


INVOICE NUMBER 12388 WEEK NUMBER: 1


[TABLE="width: 500"]
<tbody>[TR]
[TD]DOCKET [/TD]
[TD]DATE[/TD]
[TD]SOURCE[/TD]
[TD]LITRES[/TD]
[TD]WEEKEND[/TD]
[TD]AMOUNT[/TD]
[/TR]
[TR]
[TD]DD-2180[/TD]
[TD]01/02/2018[/TD]
[TD]Gosford[/TD]
[TD]40000[/TD]
[TD]NO[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Is there a way to automate this when i select a week number from the invoices sheet it brings all the rows from the main table.

Please note there may be up to 30 rows for each site each week.

Thank You

 

[James Lever]

Hi,


I would use Advanced filter, with a bit of VBA to save having to keep adjusting the filter each time you want to process. The below steps and code should get you started.


You'll need to do a bit of a set up first.


Stage 1: Rename Worksheet with the data on as Main Data. I've set it so that your Data table should always be starting at A1.


Stage 2: Create an Invoice sheet. In Ranges A1:B2 Put in your filters


WEEK SOURCE
1 Gosford


Change Wk No and Source accordingly... or if you want Gosford & Mittagong on separate sheets then that's fine, once you created the first template just copy exactly to a new sheet.


Stage 3. You now need to run the advanced filter once, and select the destination. NB: you must run from the Invoice sheet. Select your point of destination. Invoice sheet Range(A13). All columns of data will for week 1, Gosford will drop in, as per below.


WEEK TIMESTAMP DOCKET DATE SOURCE LITRES WEEKEND UNITS REGO DRIVER EMAIL SEALS TANKS AMOUNT
1 26262727 DD-2180 01/02/2018 Gosford 40000 NO 356 WOS-789 Daniel shjsjs 258546 12,13 $500


Stage4: This you only have to do one time. Remove columns so they line up with your original example. Do this by each header, select header down to the bottom record > right mouse click > Delete > selecting Shift Cells left.


Stage 5: Put formula into your invoice reference to refer to Week No in A2. e.g. ="INVOICE NUMBER 12388 WEEK NUMBER: "&A2


Below is what you should have:


Columns A & B (Rows 1 & 2) this is your filter.
WEEK SOURCE
1 Gosford




INVOICE FOR GOSFORD AND WOY WOY


INVOICE NUMBER 12388 WEEK NUMBER: 1


DOCKET DATE SOURCE LITRES WEEKEND AMOUNT
DD-2180 01/02/2018 Gosford 40000 NO $500




Stage 6. You don't have to use VBA, you run advanced filter manually without this, but it just makes life a few clicks shorter.


Dim c As Range


Set c = Sheets("Main Data").Range("A1").CurrentRegion


c.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:B2"), CopyToRange:=Range("A13:F13"), Unique:= _
False
Application.CutCopyMode = False

 

[jim may]

First, I modified your Table adding the Subsequent Invoice Number Assigned So you know You have already Invoices. Also I INSERTED COLUMN 3 with a Formula where
I concatenate the Week & the Docket to use the result as a Primary-Key or Lookup Value..

[TABLE="width: 1269"]
<tbody>[TR]
[TD]WEEK[/TD]
[TD]TIMESTAMP[/TD]
[TD]WkDocket[/TD]
[TD]DOCKET[/TD]
[TD]DATE[/TD]
[TD]SOURCE[/TD]
[TD]LITRES[/TD]
[TD]WEEKEND[/TD]
[TD]UNITS[/TD]
[TD]REGO[/TD]
[TD]DRIVER[/TD]
[TD]EMAIL[/TD]
[TD]SEALS[/TD]
[TD]TANKS[/TD]
[TD]AMOUNT[/TD]
[TD]INVNUM[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]151526267[/TD]
[TD]1-DD-2179[/TD]
[TD]DD-2179[/TD]
[TD="align: right"]1/2/2018[/TD]
[TD]Mittagong[/TD]
[TD="align: right"]40000[/TD]
[TD]YES[/TD]
[TD="align: right"]245[/TD]
[TD]CBD-098[/TD]
[TD]Daniel[/TD]
[TD]blabla[/TD]
[TD="align: right"]262727[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]$400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]26262727[/TD]
[TD]1-DD-2180[/TD]
[TD]DD-2180[/TD]
[TD="align: right"]1/2/2018[/TD]
[TD]Gosford[/TD]
[TD="align: right"]40000[/TD]
[TD]NO[/TD]
[TD="align: right"]356[/TD]
[TD]WOS-789[/TD]
[TD]Daniel[/TD]
[TD]shjsjs[/TD]
[TD="align: right"]258546[/TD]
[TD]12,13[/TD]
[TD="align: right"]$500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]68768769[/TD]
[TD]2-DD-2181[/TD]
[TD]DD-2181[/TD]
[TD="align: right"]2/2/2018[/TD]
[TD]Mittagong[/TD]
[TD="align: right"]40000[/TD]
[TD]NO[/TD]
[TD="align: right"]245[/TD]
[TD]CBD-098[/TD]
[TD]Daniel[/TD]
[TD]sjdjdkj[/TD]
[TD="align: right"]465466[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]$400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]237272[/TD]
[TD]3-DD-2182[/TD]
[TD]DD-2182[/TD]
[TD="align: right"]3/2/2018[/TD]
[TD]Woy Woy[/TD]
[TD="align: right"]40000[/TD]
[TD]NO[/TD]
[TD="align: right"]356[/TD]
[TD]WOS-789[/TD]
[TD]Daniel[/TD]
[TD]sgsh[/TD]
[TD="align: right"]456554[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]$350[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

On A Seperate Sheet "INVOICE" I set up the Following. All you have to do is Enter 3 pieces of information -- namely 1) Invoice Number; 2) Week No; and 3) Docket No
From these 3 entries (THE YELLOW CELLS ONLY) the detail on Row 12 is automatically updated. This should get you started....


Excel 2010

	A	B	C	D	E	F	G
INVOICE FOR:
Enter:	Enter:	Enter:

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]WOY WOY[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Invoice No>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]12301[/TD]
[TD="align: center"]Week No>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]3[/TD]
[TD="align: center"]Docket>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]DD-2182[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: center"]DOCKET[/TD]
[TD="align: center"]DATE[/TD]
[TD="align: center"]SOURCE[/TD]
[TD="align: center"]LITRES[/TD]
[TD="align: center"]WEEKEND[/TD]
[TD="align: center"]AMOUNT[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: center"]DD-2182[/TD]
[TD="align: center"]03/02/2018[/TD]
[TD="align: center"]Woy Woy[/TD]
[TD="align: center"] 40,000 [/TD]
[TD="align: center"] NO [/TD]
[TD="align: center"] $ 350.00 [/TD]

</tbody>

Invoice​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]=UPPER(D12)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B12[/TH]
[TD="align: left"]=IFERROR(INDEX(MyData!$C$2:$O$5,MATCH($E$8&"-"&$G$8,MyData!$C$2:$C$5,0),MATCH(B$11,MyData!$C$1:$O$1,0)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C12[/TH]
[TD="align: left"]=IFERROR(INDEX(MyData!$C$2:$O$5,MATCH($E$8&"-"&$G$8,MyData!$C$2:$C$5,0),MATCH(C$11,MyData!$C$1:$O$1,0)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D12[/TH]
[TD="align: left"]=IFERROR(INDEX(MyData!$C$2:$O$5,MATCH($E$8&"-"&$G$8,MyData!$C$2:$C$5,0),MATCH(D$11,MyData!$C$1:$O$1,0)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E12[/TH]
[TD="align: left"]=IFERROR(INDEX(MyData!$C$2:$O$5,MATCH($E$8&"-"&$G$8,MyData!$C$2:$C$5,0),MATCH(E$11,MyData!$C$1:$O$1,0)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F12[/TH]
[TD="align: left"]=IFERROR(INDEX(MyData!$C$2:$O$5,MATCH($E$8&"-"&$G$8,MyData!$C$2:$C$5,0),MATCH(F$11,MyData!$C$1:$O$1,0)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G12[/TH]
[TD="align: left"]=IFERROR(INDEX(MyData!$C$2:$O$5,MATCH($E$8&"-"&$G$8,MyData!$C$2:$C$5,0),MATCH(G$11,MyData!$C$1:$O$1,0)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanielMachin]

Thanks for your help!!!!

Ive had a look into using the Advanced filters and it works well love it.

I tried copying the VBA into both main data sheet and the invoice sheet (just incase..... im not good at VBA) however im getting
a complie error invalid outside procedure with the Set highlighted in blue

 

[jim may]

My Approach DOES NOT use any VBA -- Only Worksheet Functions/Formulas. Jim

 

[sandy666]

maybe try two (or more if needed, it's up 2 U) PivotTables

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]WEEK[/td][td=bgcolor:#DDEBF7]1[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]SOURCE[/td][td=bgcolor:#DDEBF7]Woy Woy[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]DOCKET[/td][td=bgcolor:#DDEBF7]DATE[/td][td=bgcolor:#DDEBF7]SOURCE[/td][td=bgcolor:#DDEBF7]LITRES[/td][td=bgcolor:#DDEBF7]WEEKEND[/td][td=bgcolor:#DDEBF7]_AMOUNT[/td][td][/td][td=bgcolor:#DDEBF7]WEEK[/td][td=bgcolor:#DDEBF7]DOCKET[/td][td=bgcolor:#DDEBF7]DATE[/td][td=bgcolor:#DDEBF7]LITRES[/td][td=bgcolor:#DDEBF7]WEEKEND[/td][td=bgcolor:#DDEBF7]_AMOUNT[/td][/tr]

[tr=bgcolor:#FFFFFF][td]DD-2179[/td][td]

01/02/2018​

[/td][td]Mittagong[/td][td]

40000​

[/td][td]YES[/td][td]

400​

[/td][td][/td][td]

3​

[/td][td]DD-2182[/td][td]

03/02/2018​

[/td][td]

40000​

[/td][td]NO[/td][td]

350​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]DD-2180[/td][td]

01/02/2018​

[/td][td]Gosford[/td][td]

40000​

[/td][td]NO[/td][td]

500​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[James Lever]

Hi Daniel,

You need to put into a procedure, e.g. I would go into the VBA pane, if you don't have one just go to the menu at top "Insert" select module, copy paste the below.

Public Sub GetInvDetails()
Dim c As Range


Set c = Sheets("Main Data").Range("A1").CurrentRegion


c.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:B2"), CopyToRange:=Range("A13:F13"), Unique:= _
False
Application.CutCopyMode = False

End Sub.

 

[DanielMachin]

Thanks Mate...ill give it a crack today

 

[vcoolio]

G'day Daniel,

Here's another method using Autofilter in a Workbook_SheetChange event:-

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Range("B4")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
If Sh.Name = "Sheet1" Then Exit Sub  '---->Change sheet name to suit.

Application.ScreenUpdating = False

With Sheet1.[A1].CurrentRegion  '---->Change sheet name to suit.
        .AutoFilter 1, Sh.[B3].Value
        .AutoFilter 5, Sh.[B4].Value
        Union(.Columns("C:G"), .Columns("N")).Offset(1).Copy Sh.Range("A" & Rows.Count).End(3)(2)
        .AutoFilter
        Sh.Columns.AutoFit
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Following is the link to a sample workbook I've prepared for you to play with (I've only assumed how your actual workbook might be set up):-

http://ge.tt/3ijirOu2

Sheet1 is the data entry sheet. Sheets2 and 3 are the invoice sheets (however, you may only need to use one for invoicing purposes).
In Sheets2 and 3, cells B3 and B4 have drop down lists. B3 holds the week list and B4 holds the source list.
Select a week number from the drop down in B3 then select a source city from the drop down in B4.
Make sure that you select the source city last as this is the cell that activates the code.

On selecting the source city, the code will then filter the data in sheet1 (Columns A and E) for the selections you have made in the invoice sheets and will then transfer the relevant rows of data to the invoice sheet.

To implement the code:-

- Go to the VB Editor by pressing Alt + F11.
- Over to the left in the Project Explorer, double click on ThisWorkbook.
- In the big white code field that then appears, paste the above code.

Please test the code in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.