How to project data from 1 sheet to another but remove columns with no data below header.

[johnmpc]

Hi All,

I have an input sheet for placing clothing orders. I then have a separate output sheet that displays the data in a way i can copy and paste what is needed to send back out.

I have Small medium large Xlarge XXlarge sizes in column headers, but what i want is if any columns below the header are empty i don't want to display the column or header.

Is that possible?

 

[johnmpc]

Hurrah, It works. Thank you
However, the automated update skipping back to the first sheet bit makes it really clunky. Anything i can do apart from run the macro manually?

 

[My Aswer Is This]

Hurrah, It works. Thank you
However, the automated update skipping back to the first sheet bit makes it really clunky. Anything i can do apart from run the macro manually?

Show me the line of code that causes the skipping back to first sheet.
At the end of the script what sheet do you want as the active sheet?

 

[Joe4]

the automated update skipping back to the first sheet bit makes it really clunky.

What is clunky about it? You should only see a quick "flash/blip".

If you do not want it to happen automatically with the update of each individual cell, then you need a way to alert Excel VBA when to run it, either manually, or by some other indicator.
If you can explain how/when you want it to run, we may be able to modify the code to do that.

 

[Joe4]

Show me the line of code that causes the skipping back to first sheet.
At the end of the script what sheet do you want as the active sheet?

I can handle it, thanks.

 

[My Aswer Is This]

Here is a simple script that will take you back to where ever you want:

Sub Go_To_Sheet()
'Modified  10/6/2021  10:48:31 AM  EDT
Application.Goto Sheets("Master").Range("A1")
End Sub

 

[johnmpc]

What is clunky about it? You should only see a quick "flash/blip".

If you do not want it to happen automatically with the update of each individual cell, then you need a way to alert Excel VBA when to run it, either manually, or by some other indicator.
If you can explain how/when you want it to run, we may be able to modify the code to do that.

I have to enter anything up to 40-50 values so even the slightest hesitation slows it down.
I've currently stuck an update button on the 2nd sheet but would it be possible to have the script run when the 2nd sheet is clicked on?

 

[Joe4]

I've currently stuck an update button on the 2nd sheet but would it be possible to have the script run when the 2nd sheet is clicked on?

Yes, that can be done very easily.

Go to the 2nd sheet in Excel and right-click on the sheet tab name at the bottom of the screen.
Then select "View Code", and paste this code in the resulting VB Editor window that pops up:

Private Sub Worksheet_Activate()
    Call MyDeleteColumns
End Sub

This will automatically run this code whenever that sheet is activated.
(and be sure to delete the "Worksheet_Change" procedure, as you no longer need that)

 

[johnmpc]

Thanks for your help. Runs well and looks good now.

 

[Joe4]

You are welcome!

Note that I re-marked the solution as the post that contains the "big" procedure, as that is the one that most directly addresses the original question asked.
This last bit just is a corollary to run that code.

 

[johnmpc]

To continue with this same principle, Could someone help me to Amend the code to also remove rows that have no data between "ONE SIZE" and "OTHER" too.

Sub MyDeleteColumns()

Dim lc As Long
Dim c As Long
Dim lr As Long

Application.ScreenUpdating = False

' Copy data from one sheet to other
Sheets("Order Template").Range("Q30:AF47").Copy Sheets("Email order version").Range("J14")

' Find last column in row 1 with data (last header)
Sheets("Email order version").Activate
lc = Cells(14, Columns.Count).End(xlToLeft).Column


' Loop through all columns
For c = lc To 10 Step -1
' Check to see if last row in column is 14
If Cells(Rows.Count, c).End(xlUp).Row = 14 Then
' Delete column
Columns(c).Delete
End If
Next c

Application.ScreenUpdating = True

End Sub

This will also need the code to transfer the specific columns from this table. line numbers 30-47 Columns A-F, I, L, M + the 14 columns in the initial code above.

Code

Supplier

Product Description

Colour

Cost (Min)

Decoration

Del/garment from supplier

Suggested Sell Price 50%

Sell

Total COGS

Profit

GP%

QTY

Cost Total

Sale Total

Profit Total

One Size

S

M

L

XL

2XL

3XL

4XL

5XL

OTHER

Decoration 1

Position 1

Decoration 2

Position 2

Decoration 3

Postition 3

Orn3200

ORN

Albatross Classic Fleece

Navy

£7.10​

£1.25​

£16.70​

£15.00​

£8.35​

£6.65​

44.33%​

20​

£142.00​

£300.00​

£133.00​

10​

10​

2MAN01

Left Chest

ORN1000

ORN

Plover Premium T-Shirt

Navy

£1.90​

£1.25​

£6.30​

£5.25​

£3.15​

£2.10​

40.00%​

15​

£28.50​

£78.75​

£31.50​

15​

2MAN01

Left Chest

PENbb45

Pencarrie

B/field Original Cuffed Beanie

French Navy

£1.35​

£1.25​

£5.20​

£5.75​

£2.60​

£3.15​

54.78%​

10​

£13.50​

£57.50​

£31.50​

10​

2MAN01

Centre Front

IT097474

Newitts

NIKE PARK 20 SENIOR WINTER JACKET

Obsidian

£66.63​

£2.00​

£1.65​

£140.56​

£110.00​

£70.28​

£39.72​

36.11%​

3​

£199.89​

£330.00​

£119.16​

3​

2MAN01

Left Chest

Initials Print

Right Chest

2111036

United Brands

Brinkley Parka

Navy

£77.90​

£2.00​

£4.00​

£167.80​

£136.00​

£83.90​

£52.10​

38.31%​

1​

£77.90​

£136.00​

£52.10​

1​

2MAN01

Left Chest

Initials Print

Right Chest

Total

Total

49​

£902.25​

£367.26​

£1,082.70​

40.70%​