PDA

View Full Version : Export to New Tab



spaz9876
11-02-2006, 09:15 AM
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
11-02-2006, 09:59 AM
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
11-02-2006, 10:02 AM
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
11-02-2006, 02:38 PM
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
11-02-2006, 02:47 PM
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 11 21.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
11-02-2006, 03:37 PM
Can you post a sample in a workbook? Use Manage Attachments in the Go Advanced section.

spaz9876
11-02-2006, 03:46 PM
Ok here it is - thank you for helping!

I only copied 4 parts.

mdmackillop
11-02-2006, 04:10 PM
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

spaz9876
11-02-2006, 04:29 PM
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
11-02-2006, 05:01 PM
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.


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

spaz9876
11-03-2006, 10:57 AM
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.

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

spaz9876
11-03-2006, 10:59 AM
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
11-03-2006, 11:10 AM
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:

Columns("A:Z").AutoFilter Field:=3, Criteria1:="<>"



you could use something like:

With Cells
.Select
.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With

to remove the formula's before you delete the row.

and I think this part resizes your columns:

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)

spaz9876
11-03-2006, 01:45 PM
none of those seemed to help :(

mdmackillop
11-03-2006, 03:51 PM
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
11-03-2006, 04:15 PM
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
11-03-2006, 04:48 PM
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
11-03-2006, 06:50 PM
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
11-04-2006, 04:51 AM
Try this

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:D").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

spaz9876
11-06-2006, 09:09 AM
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?

mdmackillop
11-06-2006, 10:39 AM
Hi Spaz,
A small sample of your price list with an example of your desired output would make it easier to assist.
Regards
MD
There is a logic behind my request for further info after all! I'm still not clear what you are after.

spaz9876
11-06-2006, 03:37 PM
Ok attached are 2 files - the result it does now and the result I would like it to show. It seems to get rid of all the formats and any merged cells in the copy. I did a "paste special" and then "all" but then it gives me reference errors when I just want the values, not the formulas.

I also have a problem now with the filter being removed from the "C-SEPMASTER" once it does the export and when you put the filter back, it doesn't filter correctly.

spaz9876
11-06-2006, 03:38 PM
Result requested.

mdmackillop
11-06-2006, 05:01 PM
Here's my final input to this question. Use a template sheet, properly formatted to display your data and copy the required cells to the appropriate locations. If you had given this output, such as it is, when requested, we could have got here a lot sooner, without time wasted on irrelevant solutions. I've no idea what your filter problem is. You could work round it by creating a temporary copy of your source sheet and running the macro on that.
Regards
MD

spaz9876
11-06-2006, 08:56 PM
Thank you for all your assistance. I apoligize if I was unable to explain myself clearly. I do appreaciate all your help.
The code works except for the filter meaning once the code runs, the filter seems to remove itself and when I put it back, it doesnt filter correctly.

lilstevie
11-15-2006, 07:22 AM
MD,

I am a newbie in VBA but am looking to streamline my excel workbooks. Parts of this code would work great for me if you can help tweak it. Basically I have a worksheet in excel that I need to have all of the data copied to another worksheet (name based on the user input) and then have the data deleted from the initial worksheet. No sorting or filtering is needed.

Thanks for all of your help // Steve

spaz9876
11-15-2006, 07:52 AM
I would love to be able to use this coding. Everything works great except the filtering so I havent been able to use it and I think MD got too frustrated with me (I don't seem to explain myself well).

lilstevie
11-15-2006, 12:16 PM
Anyone ??

lucas
11-15-2006, 01:00 PM
MD,

I am a newbie in VBA but am looking to streamline my excel workbooks. Parts of this code would work great for me if you can help tweak it. Basically I have a worksheet in excel that I need to have all of the data copied to another worksheet (name based on the user input) and then have the data deleted from the initial worksheet. No sorting or filtering is needed.

Thanks for all of your help // Steve
Best to learn from spaz's experience....post a workbook with 2 sheets. One with what you have and one with what you want the results to look like. Folks visiting here are volunteers and don't always have a lot of time to try to figure out what your trying to do. Please make it easy for them to help you.

For instance you say: I need to have all of the data copied to another worksheet

does that mean all of the data....because that just doesn't make much sense when you think about it. You would be creating a new sheet identical to the first one and then deleting the info from the first one.....see what I mean?

lilstevie
11-15-2006, 01:17 PM
Yes, i know but that is what i need it to do. I'm working with some computer challenged people. Attached is the original file with a macro (master) that I pieced together to import and process a monthly text file form our FTP.

I wanted to add the VBA to the end of the macro and have it copy the new worksheet (input) into a worksheet with the user specified name (ie - Oct06) and then clear everything from the input worksheet leaving it ready for the next use.

lilstevie
11-15-2006, 01:29 PM
Attached is the "wanted result"

lilstevie
11-15-2006, 01:32 PM
Here is the text file that I'm working with importing.

lucas
11-15-2006, 01:41 PM
So...input sheet will be blank?

Try again Stevie.....this time just one workbook with sample data in the input sheet and a second sheet showing what you wish to copy and to where....I understand that you wish to clear the input sheet after the operation.

lilstevie
11-15-2006, 02:25 PM
Steve,

Sorry for the confusion:
The complete file is to big to post, can you just open up master old and run the master macro? Select the RADMODLOCT.txt file and you will get the current end result with the data on the INPUT worksheet. Compare that file to the master new to see what I would like to end with.

lilstevie
11-15-2006, 02:35 PM
Steve,

Since I changed the file name you will have to edit the macro with it hangs up. Just change it to Windows("MASTER old.xls").Activate.

Thanks Again for your time and help with this.

lucas
11-15-2006, 02:51 PM
ok....will try to take a look at this again....

lucas
11-15-2006, 03:02 PM
Ok..I have an idea. Can we just rename the input sheet to user defined name and then create a new sheet named input to receive the next transfer?

lucas
11-15-2006, 03:03 PM
the input sheet after the data transfer from the txt file seem to be identical to the Master new oct 6 sheet.....right?

lilstevie
11-15-2006, 03:09 PM
Steve,

1. Yes renaming the input sheet after the data transfer and creating a new input sheet would give the same result.

2. Yep, I just deleted some of the rows and formulas in the Oct06 sheet to reduce the file size but you get the idea..

lucas
11-15-2006, 03:44 PM
This works for me...see stuff added to the end of the master sub
still needs some cleaning up but check it out and we can go from there.

lilstevie
11-16-2006, 06:11 AM
Works like a charm. Thanks Steve. Know of any good VBA tutorial web pages?

lucas
11-16-2006, 07:47 AM
Glad you got a solution....

VBAExpress offers training:
http://vbaexpress.com/training/

read the posts here and following the better coders to learn best methods. Search the forums and the knowledgebase. I think there are threads addressing VBA tutorials, etc. also read our articles section.

mdmackillop
11-16-2006, 02:47 PM
Hi LilStevie
We all started off recording code. The next step is to refine it, getting rid of the verbiage. One of the first things to realise is that is very rarely neccessary to "select" a cell. Usually the only reason is to leave the cursor in a particular location when the code is completed. Cells can be manipulated without this action. The following will carry out the same basic task. 'Before' is a small extract from your code.

Sub Before()
Columns("A:A").Select
ActiveWindow.LargeScroll ToRight:=1
Columns("A:U").Select
Columns("A:U").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=-15
Range("D24").Select
ActiveWindow.SmallScroll ToRight:=3
Columns("F:G").Select
Selection.Cut
ActiveWindow.LargeScroll ToRight:=-1
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=6
Columns("H:I").Select
Selection.Cut
ActiveWindow.LargeScroll ToRight:=-1
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=9
Columns("K:L").Select
Selection.Cut
ActiveWindow.LargeScroll ToRight:=-2
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=10
Columns("N:O").Select
Selection.Cut
ActiveWindow.LargeScroll ToRight:=-2
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=15
End Sub

Sub After()
Columns("A:U").EntireColumn.AutoFit
Columns("F:G").Cut
Columns("C:C").Insert
Columns("H:I").Cut
Columns("E:E").Insert
Columns("K:L").Cut
Columns("A:A").Insert
Columns("N:O").Cut
Columns("I:I").Insert
End Sub

lilstevie
11-17-2006, 04:58 AM
MD,

Thanks for the tip. Looks very streamlined and easier to interpret then the macro recorder. I do have a long way to go, but am looking forward to learning the tricks of VBA.

jzuck
11-17-2006, 01:37 PM
nt