create new tab taking data from one column

[sbrown64]

Hi
I have a spreadsheet which has many tabs at the bottom for different companies. Sheet1 is the master where all info is recorded on a daily basis, I run the macro and it copies the data to the relevant company tab, all that is ok.
Now I have been asked that a new page is created with just the contents of one column selected. So for instance column G needs to be selected from the master sheet and a new sheet created called MS,Aldi Separation and any entries that have figures to be copied to a new sheet. The list below was done manually by copy and paste from the master sheet to a new sheet added called MSaldi separation

No.	Invoice Date	Company	MSAldi Separation
6.0	Year to 30th June 2015
*	30/06/2014	Forty Shillings	11,026.33
6.4	25/07/2014	40 Shillings	3,567.50
		TOTAL	14,593.83	14,593.83
5.0	Year to 30th June 2014
5.9	28/02/2014	Bidwells	1,665.48
5.12	31/03/2014	Bidwells	1,798.40
5.14	30/04/2014	Bidwells	2,480.25
5.16	31/05/2014	Bidwells	7,762.62
5.18	11/06/2014	Bidwells	7,200.00
5.21	30/06/2014	Bidwells	8,173.20
6.0	Year to 30th June 2015
6.5	31/07/2014	Bidwells	4,158.45
6.22	31/01/2015	Bidwells	585.00
8.0	Year to 30th June 2017
8.14	28/02/2017	Bidwells	9,899.00
8.23	30/04/2017	Bidwells	3,725.00
8.25	31/05/2017	Bidwells	3,641.91
8.31	30/06/2017	Bidwells	1,493.43
9.0	Year to 30th June 2018
9.5	31/08/2017	Bidwells	575.00
9.6	31/07/2017	Bidwells	870.00
9.10	30/09/2017	Bidwells	533.50
9.0	Year to 30th June 2018
9.27	30/11/2017	Bidwells	350.00
9.38	31/01/2018	Bidwells	1,647.00
9.0	Year to 30th June 2018
9.46	28/02/2018	Bidwells	1,192.00
9.47	31/03/2018	Bidwells	1,416.00
10.00	Year to 30th June 2019
10.60	31/10/2018	Bidwells	200.00
		TOTAL		59,366.24

<tbody>
</tbody>

I hope I have made my self clear, I look forward to receiving some help.
Regards
Stephen

 

[onlyadrafter]

Hello,

Not totally clear, but lets try this to start with

Sub CREATE_NEW_TAB()
    Sheets.Add
    ActiveSheet.Name = "MS ALDI SEPARATION"
    MY_DEST = ActiveSheet.Name
    Sheets("MASTER").Select
    Range("G2:G" & Range("G" & Rows.Count).End(xlUp).Row).SpecialCells(xlConstants).Copy
    Sheets(MY_DEST).Range("A1").PasteSpecial (xlPasteValues)
End Sub

post back with any issues/problems.

 

[sbrown64]

Hi

I tried the code and it created a new sheet, but all it copied across into column A was the figures, is there a way to copy the entire row, so it would show the entire line a bit like my example above?

Regards

Stephen

 

[onlyadrafter]

Hello,

Not 100% clear, what column are you looking for that has data? Is it the first column (No.) or either of the others. I understand that if this column has data then the entire row needs to be copied.

Won't that just copy the entire table though?

 

[sbrown64]

Hi

This is the link to the entire file

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://1drv.ms/x/s!Aq3WqOz73fYygYUVGCSdsQ9NJh76_w?e=NVvKQs

As you can see the new tab but only numbers in column A, in the Royston costs sheet (the input sheet) it is basically anything with an amount in column G and ideally then list them in name order with a subtotal. The example i provided above was a manually created one with entries copied and pasted as when required.

Regards

Stephen
[/FONT]

 

[sbrown64]

I have tried creating a pivot table for the master sheet and when I try to select the ms/aldi column this is what happens, but it is showing the blanks. how do i get rid of them showing?

[TABLE="width: 464"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Sum of M&S/Aldi/Retail[/TD]
[TD]Sum of Total[/TD]
[/TR]
[TR]
[TD]June 30 2018[/TD]
[TD="align: right"]$2,278,809.34[/TD]
[TD="align: right"]$4,266,749.10[/TD]
[/TR]
[TR]
[TD]Anglian Water[/TD]
[TD][/TD]
[TD="align: right"]$4,782.58[/TD]
[/TR]
[TR]
[TD]Bidwells[/TD]
[TD="align: right"]$6,583.50[/TD]
[TD="align: right"]$18,788.25[/TD]
[/TR]
[TR]
[TD]BLP Insurance[/TD]
[TD="align: right"]$5,000.00[/TD]
[TD="align: right"]$5,000.00[/TD]
[/TR]
[TR]
[TD]Breheny[/TD]
[TD="align: right"]$1,837,112.10[/TD]
[TD="align: right"]$3,282,004.70[/TD]
[/TR]
[TR]
[TD]Cannon[/TD]
[TD][/TD]
[TD="align: right"]$1,698.75[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]