Copying Rows from one table to another based on conditions

[DanielMachin]

Hi Guys,

I currently have a main data table as below

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

<tbody>
</tbody>

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

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

<tbody>
</tbody>

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..

WEEK

TIMESTAMP

WkDocket

DOCKET

DATE

SOURCE

LITRES

WEEKEND

UNITS

REGO

DRIVER

EMAIL

SEALS

TANKS

AMOUNT

INVNUM

1

151526267

1-DD-2179

DD-2179

1/2/2018

Mittagong

40000

YES

245

CBD-098

Daniel

blabla

262727

12

$400

1

26262727

1-DD-2180

DD-2180

1/2/2018

Gosford

40000

NO

356

WOS-789

Daniel

shjsjs

258546

12,13

$500

2

68768769

2-DD-2181

DD-2181

2/2/2018

Mittagong

40000

NO

245

CBD-098

Daniel

sjdjdkj

465466

14

$400

3

237272

3-DD-2182

DD-2182

3/2/2018

Woy Woy

40000

NO

356

WOS-789

Daniel

sgsh

456554

14

$350

<tbody>
</tbody>

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
4
5
6		INVOICE FOR:	WOY WOY
7			Enter:		Enter:		Enter:
8		Invoice No>	12301	Week No>	3	Docket>	DD-2182
9
10
11		DOCKET	DATE	SOURCE	LITRES	WEEKEND	AMOUNT
12		DD-2182	03/02/2018	Woy Woy	40,000	NO	$ 350.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Invoice​

Worksheet Formulas Cell Formula C6 =UPPER(D12) B12 =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)),"") C12 =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)),"") D12 =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)),"") E12 =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)),"") F12 =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)),"") G12 =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)),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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

WEEK

1

SOURCE

Woy Woy

DOCKET

DATE

SOURCE

LITRES

WEEKEND

_AMOUNT

WEEK

DOCKET

DATE

LITRES

WEEKEND

_AMOUNT

DD-2179

01/02/2018​

Mittagong

40000​

YES

400​

3​

DD-2182

03/02/2018​

40000​

NO

350​

DD-2180

01/02/2018​

Gosford

40000​

NO

500​

 

[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.