Saving different files based upon data in particular column

I have a spreadsheet with lots of data and what we normally do now is take that data (based upon the value in column H(which is our Vendor ID)), cut and paste each row that matches which vendor we're looking for into new seperate files and save it. It is very tedious work. I'm curious if it is possible to do this in a macro in which one would have it so that it takes the data based upon the values in column H and makes seperate files for each different value with the data from each corresponding row. I hope that makes sense.

There has been lots of work done on this:

Try this link and see if you can find what you need
http://www.j-walk.com/ss/excel/links/xllinks2.htm

Or go to google search.

You are basically looking for looping code that will check a cell value and
than transfer the data to a new workbook (or another workbook or another
worksheet).

--
steveB

Remove "AYN" from email to respond
"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> I have a spreadsheet with lots of data and what we normally do now is
> take that data (based upon the value in column H(which is our Vendor
> ID)), cut and paste each row that matches which vendor we're looking
> for into new seperate files and save it. It is very tedious work. I'm
> curious if it is possible to do this in a macro in which one would have
> it so that it takes the data based upon the values in column H and
> makes seperate files for each different value with the data from each
> corresponding row. I hope that makes sense.
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
> http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=392476
>

Hi DKY

See
http://www.rondebruin.nl/copy5.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"DKY" <[email protected]> wrote in message news:[email protected]...
>
> I have a spreadsheet with lots of data and what we normally do now is
> take that data (based upon the value in column H(which is our Vendor
> ID)), cut and paste each row that matches which vendor we're looking
> for into new seperate files and save it. It is very tedious work. I'm
> curious if it is possible to do this in a macro in which one would have
> it so that it takes the data based upon the values in column H and
> makes seperate files for each different value with the data from each
> corresponding row. I hope that makes sense.
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=392476
>

Ron,

I'm using your code titled 'Create a new workbook for all Unique values' it looks like it will do exactly what I want I just have to configure it and I'm having a difficult time.

I'm supposed to change only this section, right?

Please Login or Register  to view this content.

~There aren't any other sections?

~Also, when I change Sheet1 to the actual name of my sheet it gives a debug error.

~and I'm not sure how to do the range part. I picked H2 because that's the column I want to get the values from but will it get all the information behind it? Like in columns A:G?

Oops, I forgot to add the title. I'm using the code titled 'Create a new workbook for all Unique values'. Thanks

I got it figured out. I changed ThisWorkbook.Sheets("Sheet1") to Sheets("Sheet1") and for my range it looks like this.

Please Login or Register  to view this content.

Thanks for the code Ron. I was wondering, how would I add to this macro to not only save as a .xls file but to save the same information in a .txt file, then move on to the next value?

Look in the VBA help for SaveAs and read about FileFormat
FileFormat:=xlText


--
Regards Ron de Bruin
http://www.rondebruin.nl


"DKY" <[email protected]> wrote in message news:[email protected]...
>
> I got it figured out. I changed ThisWorkbook.Sheets("Sheet1") to
> Sheets("Sheet1") and for my range it looks like this.
>
> Code:
> --------------------
> LaRow = Cells(Rows.Count, "H").End(xlUp).Row
> Set rng = ws1.Range("H2:H" & LaRow).CurrentRegion '<<< Change
> --------------------
>
> Thanks for the code Ron. I was wondering, how would I add to this
> macro to not only save as a .xls file but to save the same information
> in a .txt file, then move on to the next value?
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=392476
>

I tried putting this

'text
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Columns("M:M").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("C:AA").Select
Selection.Delete Shift:=xlToLeft
Columns("A:B").Select
Columns("A:B").EntireColumn.AutoFit
Range("A1").Select


WBNew.SaveAs FileFolder & cell.Value & "_Surplus_" & FORMAT(Now, "yyyy-mmm-dd"), FileFormat:=xlText _
, CreateBackup:=False
WBNew.Close False
'text

after this part

WBNew.SaveAs FileFolder & cell.Value & "_Surplus_" & FORMAT(Now, "yyyy-mmm-dd")
WBNew.Close False

but its giving me errors. I have something wrong. I want it to make the excel file, then delete some rows and make a txt file with the same name format as the excel file. Not working for me though.

Try this

WBNew.SaveAs FileFolder & cell.Value & "_Surplus_" & FORMAT(Date,
"yyyy-mmm-dd") & ".txt", FileFormat:=xlText _
, CreateBackup:=False


--
Regards Ron de Bruin
http://www.rondebruin.nl


"DKY" <[email protected]> wrote in message news:[email protected]...
>
> I tried putting this
>
> 'text
> Columns("A:C").Select
> Range("C1").Activate
> Selection.Delete Shift:=xlToLeft
> Columns("M:M").Select
> Selection.Cut
> Columns("A:A").Select
> Selection.Insert Shift:=xlToRight
> Columns("C:AA").Select
> Selection.Delete Shift:=xlToLeft
> Columns("A:B").Select
> Columns("A:B").EntireColumn.AutoFit
> Range("A1").Select
>
>
> WBNew.SaveAs FileFolder & cell.Value & "_Surplus_" & FORMAT(Now,
> "yyyy-mmm-dd"), FileFormat:=xlText _
> , CreateBackup:=False
> WBNew.Close False
> 'text
>
> after this part
>
> WBNew.SaveAs FileFolder & cell.Value & "_Surplus_" &
> FORMAT(Now, "yyyy-mmm-dd")
> WBNew.Close False
>
> but its giving me errors. I have something wrong. I want it to make
> the excel file, then delete some rows and make a txt file with the same
> name format as the excel file. Not working for me though.
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=392476
>

First start by cleaning up your code and getting rid of all the select...

Columns("A:C").Delete
Columns("A:A").Insert
Columns("N:N").Copy _
Destination:=Range("A1")
Columns("N:N").ClearContents

Columns("C:AA").Delete
Columns("A:B").EntireColumn.AutoFit


--
steveB

Remove "AYN" from email to respond
"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> I tried putting this
>
> 'text
> Columns("A:C").Select
> Range("C1").Activate
> Selection.Delete Shift:=xlToLeft
> Columns("M:M").Select
> Selection.Cut
> Columns("A:A").Select
> Selection.Insert Shift:=xlToRight
> Columns("C:AA").Select
> Selection.Delete Shift:=xlToLeft
> Columns("A:B").Select
> Columns("A:B").EntireColumn.AutoFit
> Range("A1").Select
>
>
> WBNew.SaveAs FileFolder & cell.Value & "_Surplus_" & FORMAT(Now,
> "yyyy-mmm-dd"), FileFormat:=xlText _
> , CreateBackup:=False
> WBNew.Close False
> 'text
>
> after this part
>
> WBNew.SaveAs FileFolder & cell.Value & "_Surplus_" &
> FORMAT(Now, "yyyy-mmm-dd")
> WBNew.Close False
>
> but its giving me errors. I have something wrong. I want it to make
> the excel file, then delete some rows and make a txt file with the same
> name format as the excel file. Not working for me though.
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
> http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=392476
>

Run-time error '13':

Type mismatch

then it highlights the following code.

Please Login or Register  to view this content.

So, in essence, here's what I have

Please Login or Register  to view this content.

You close it after saving it to a xls
> WBNew.Close False

Then you run this code

> Columns("A:C").Delete
> Columns("A:A").Insert
> Columns("N:N").Copy _
> Destination:=Range("A1")
> Columns("N:N").ClearContents
>
> Columns("C:AA").Delete
> Columns("A:B").EntireColumn.AutoFit
> Range("A1").Select

You run this on the activesheet on that moment

Then you want to save as a txt file and WBNew is closed so that is not possible

Copy your whole macro in this thread then i change it for you



--
Regards Ron de Bruin
http://www.rondebruin.nl


"DKY" <[email protected]> wrote in message news:[email protected]...
>
> So, in essence, here's what I have
>
> Code:
> --------------------
> WBNew.SaveAs FileFolder & cell.Value & "_Surplus_" & FORMAT(Now, "yyyy-mmm-dd")
> WBNew.Close False
> 'text
> Columns("A:C").Delete
> Columns("A:A").Insert
> Columns("N:N").Copy _
> Destination:=Range("A1")
> Columns("N:N").ClearContents
>
> Columns("C:AA").Delete
> Columns("A:B").EntireColumn.AutoFit
> Range("A1").Select
>
> WBNew.SaveAs FileFolder & cell.Value & "_Surplus_" & FORMAT(Date, "yyyy-mmm-dd") & ".txt", FileFormat:=xlText _
> , CreateBackup:=False
> 'WBNew.SaveAs FileFolder & cell.Value & "_Surplus_" & FORMAT(Now, "yyyy-mmm-dd"), FileFormat:=xlText _
> ' , CreateBackup:=False
> WBNew.Close False
> 'text
> Next
> --------------------
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=392476
>

Try doing a save-as with the recorder on. Than check out the code.

Replace all of the hard coded names and such with variables and try it out.
Double check that your variables are properly "Dim" 'd....

Thanks for including my code...

--
steveB

Remove "AYN" from email to respond
"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> So, in essence, here's what I have
>
> Code:
> --------------------
> WBNew.SaveAs FileFolder & cell.Value & "_Surplus_" &
> FORMAT(Now, "yyyy-mmm-dd")
> WBNew.Close False
> 'text
> Columns("A:C").Delete
> Columns("A:A").Insert
> Columns("N:N").Copy _
> Destination:=Range("A1")
> Columns("N:N").ClearContents
>
> Columns("C:AA").Delete
> Columns("A:B").EntireColumn.AutoFit
> Range("A1").Select
>
> WBNew.SaveAs FileFolder & cell.Value & "_Surplus_" & FORMAT(Date,
> "yyyy-mmm-dd") & ".txt", FileFormat:=xlText _
> , CreateBackup:=False
> 'WBNew.SaveAs FileFolder & cell.Value & "_Surplus_" & FORMAT(Now,
> "yyyy-mmm-dd"), FileFormat:=xlText _
> ' , CreateBackup:=False
> WBNew.Close False
> 'text
> Next
> --------------------
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
> http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=392476
>

Instead of posting my code I just made a different macro for the txt files. So, now I have two. No big deal and I don't have to bother you guys so much. LOL Thanks for the help.

Originally Posted by Ron de Bruin

You close it after saving it to a xls
> WBNew.Close False

Then you run this code

> Columns("A:C").Delete
> Columns("A:A").Insert
> Columns("N:N").Copy _
> Destination:=Range("A1")
> Columns("N:N").ClearContents
>
> Columns("C:AA").Delete
> Columns("A:B").EntireColumn.AutoFit
> Range("A1").Select

You run this on the activesheet on that moment

Then you want to save as a txt file and WBNew is closed so that is not possible

Copy your whole macro in this thread then i change it for you



--
Regards Ron de Bruin
http://www.rondebruin.nl


[/color]