Export to New Tab

[spaz9876]

I have a huge price list that is in excel - has about 6000 line items.
When we price out parts for a customer, we put the quantity in one column and then filter that column to "non blanks" so it only shows the parts in that column. Is there any way to take those line items(rows) as well as only certain columns (that are behind the page breaks) within those rows and export them to a new tab with a click of a hand made button? When the parts are sold, I want to print out our cost and labor associated with those items without having to move page break lines or copying and pasting into a new document. Anyone help?

[SamT]

Spaz,

I do the same thing when I order parts.

First I broke down the parts list into more managable sized sub lists. In my case, for example; Lumber: 2x4 - 2x 6- 2x8, then 2x4:6' long - 8' long - etc; Nails: Gavanized (by size) - common (by size); etc

Then, on my PO sheet, I wrote a SelectionChange sub that popped up a ComboBox that showed the List Titles. Selecting a List title popped up another CB that showed List items, or sublist titles. Selecting from the final CB filled in the PO line.

In your case, I would make a Print for Customer button that first set "My Cost" and My Labor" cells to Visible=False.

Can you attach an empty workshhet that has column headers and the parts list, no prices or anything confidential?

SamT

[spaz9876]

Unfortunaty, we have over 6000 items with new ones always been added daily so it would take me forever to make sublists.

I also don't know how to do the selection change or make a print for customer button

[mdmackillop]

Hi Spaz,
A small sample of your price list with an example of your desired output would make it easier to assist.
Regards
MD

[spaz9876]

Product NumberDescriptionQty Unit Price Extended Price Price Single Aggregate Price Total Aggregate GSA Price List Price Unit Cost Times XMSRP MSLP or GSA Approved Rate GSA Discount% Margin Unit Margin Total Margin Total Cost BasicTech Program-ing TrainingEngin-eeringProject Mgmt.Labor Single AggregateLabor Total Aggregate (inc. discount)1234Part Number Description1 $179.78 $179.78 $325.78 $325.78 $140.23 $179.78 $89.89 2 $179.78 78%50.00% $89.89 $89.89 $89.89 1121.92
I hope this works. Everything where the header is in grey is what goes to the client. When it is sold, I want to be able to export just the line items that have a quantity in the quantity column and also show SOME of the columns to the right.

[mdmackillop]

Can you post a sample in a workbook? Use Manage Attachments in the Go Advanced section.

[spaz9876]

Ok here it is - thank you for helping!

I only copied 4 parts.

[mdmackillop]

[VBA]Option Explicit
Sub PartsList()
Dim c As Range, cel As Range
Dim i As Long
Columns("A:Z").AutoFilter Field:=3, Criteria1:="<>"
Set c = Range(Cells(2, 1), Cells(Rows.Count, _
1).End(xlUp)).SpecialCells(xlCellTypeVisible)
i = 1
For Each cel In c
i = i + 1
cel.Resize(, 10).Copy Sheets(2).Cells(i, 1)
cel.Offset(, 18).Resize(, 8).Copy Sheets(2).Cells(i, 11)
Next
Columns("A:Z").AutoFilter
End Sub
[/VBA]

[spaz9876]

I get a runtime error 1004.
How do I create a button that when you press it, it runs the code and opens up a new tab within the document with the results?

[mdmackillop]

I get a runtime error 1004.

On what line?

The simplest button is on the Forms toolbar. Use the Button tool to draw a button on the worksheet and then assign the macro.

[VBA]
Option Explicit
Sub PartsList()
Dim c As Range, cel As Range
Dim i As Long
Dim shName As String
Dim ThsSht As Worksheet
Application.ScreenUpdating = False
Set ThsSht = ActiveSheet
Columns("A:Z").AutoFilter Field:=3, Criteria1:="<>"
Set c = Range(Cells(2, 1), Cells(Rows.Count, _
1).End(xlUp)).SpecialCells(xlCellTypeVisible)
Sheets.Add
shName = InputBox("Enter sheet name", , "Result")
ActiveSheet.Name = shName
With Sheets(shName)
ThsSht.Rows("1:1").Copy .Cells(1, 1)
ThsSht.Rows("1:1").Copy
.Cells(1, 1).PasteSpecial Paste:=xlPasteColumnWidths
.Cells(1, 1).Select
.Columns("K:R").Delete
i = 1
For Each cel In c
i = i + 1
cel.Resize(, 10).Copy .Cells(i, 1)
cel.Offset(, 18).Resize(, 8).Copy .Cells(i, 11)
Next
End With
ThsSht.Columns("A:Z").AutoFilter
Application.ScreenUpdating = True
End Sub

[/VBA]

[spaz9876]

Ok that kinda works - it takes EVERY part though, not just the ones that have quantities. It also puts a reference error in the cells that have formulas.
Any way make it just paste the values? Also, it changes the column width.

This is the VBA that I edited from yours because their are 5 columns before the columns that need to be copied and the quantity column is column H.

[VBA]Sub Button5183_Click()
Dim h As Range, cel As Range
Dim i As Long
Dim shName As String
Dim ThsSht As Worksheet
Application.ScreenUpdating = False
Set ThsSht = ActiveSheet
Columns("A:Z").AutoFilter Field:=3, Criteria1:="<>"
Set h = Range(Cells(2, 1), Cells(Rows.Count, _
1).End(xlUp)).SpecialCells(xlCellTypeVisible)
Sheets.Add
shName = InputBox("Enter sheet name", , "Result")
ActiveSheet.Name = shName
With Sheets(shName)
ThsSht.Rows("1:1").Copy .Cells(1, 1)
ThsSht.Rows("1:1").Copy
.Cells(1, 1).PasteSpecial Paste:=xlPasteColumnWidths
.Cells(1, 1).Select
.Columns("K:R").Delete
i = 1
For Each cel In h
i = i + 1
cel.Offset(, 5).Resize(, 10).Copy .Cells(i, 1)
cel.Offset(, 18).Resize(, 8).Copy .Cells(i, 11)
Next
End With
ThsSht.Columns("A:Z").AutoFilter
Application.ScreenUpdating = True
End Sub
[/VBA]

[spaz9876]

Also, Is there a way to put the button on another tab but still have the macro refer to the sheet I need? The tab I want it to refer to is called "C-SEPMASTER"

[lucas]

If you clear the contents of one of the quan cells....not just put a 0 it works because its looking for anything in that cell:
[VBA]
Columns("A:Z").AutoFilter Field:=3, Criteria1:="<>"
[/VBA]


you could use something like:
[VBA]
With Cells
.Select
.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
[/VBA]
to remove the formula's before you delete the row.

and I think this part resizes your columns:
[VBA]
For Each cel In h
i = i + 1
cel.Offset(, 5).Resize(, 10).Copy .Cells(i, 1)
cel.Offset(, 18).Resize(, 8).Copy .Cells(i, 11)

[/VBA]

[spaz9876]

none of those seemed to help

[mdmackillop]

and the quantity column is column H.

Column H heading in the sample you posted is "GSA Price". While I have some facility in VBA, my psychic abilities are minimal. Please refer to post #4 and #10. Also, let's get the code working before worrying about buttons, and if you want to refer to specific sheet names, please include them in the info you provide.
Regards
MD

[spaz9876]

Ok lets try this again. I didn't copy all the columns the first time.
Here is the whole top half of the spreadsheet tab. the cells I made yellow are the columns I want to export if the Qty column (H) is not blank.

[mdmackillop]

While not wanting to appear pedantic, I'm still awaiting a full reponse to posts #4 and #10 in order to reply properly to your questions.

[spaz9876]

Ok post #4 was asking for a sample which I just did.
Post #10 was about the error - I don't get that error anymore since I edited it to the coding in post #11

[mdmackillop]

Try this
[vba]
Option Explicit
Sub PartsList()
Dim c As Range, cel As Range
Dim i As Long, LRw As Long
Dim shName As String
Dim DataSht As Worksheet
Application.ScreenUpdating = False
Set DataSht = Sheets("C-SEPMASTER")
With DataSht
LRw = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("A6:BU" & LRw).AutoFilter Field:=8, Criteria1:="<>"
Set c = Range(.Cells(9, 8), .Cells(LRw, _
8)).SpecialCells(xlCellTypeVisible)
End With
Sheets.Add
shName = InputBox("Enter sheet name", , "Result")
ActiveSheet.Name = shName
With Sheets(shName)
DataSht.Rows("6:6").Copy .Cells(1, 1)
DataSht.Rows("6:6").Copy
.Cells(1, 1).PasteSpecial Paste:=xlPasteColumnWidths
.Cells(1, 1).Select
.Columns("AF:BU").Delete
.Columns("T:W").Delete
.Columns("A").Delete
i = 1
For Each cel In c
i = i + 1
cel.Offset(, -3).Resize(, 15).Copy
.Cells(i, 1).PasteSpecial Paste:=xlValues
cel.Offset(, 16).Resize(, 8).Copy
.Cells(i, 16).PasteSpecial Paste:=xlValues
Next
.Cells(1, 1).Select
End With
DataSht.Range("A6:BU" & LRw).AutoFilter
Application.ScreenUpdating = True
End Sub
[/vba]

[spaz9876]

Wonderful!
That worked!
One thing it didnt do was the row widths and text wrapping as well as the lines on the cells. How do I add that?