| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving different files based upon data in particular column
I have a spreadsheet with lots of data and what we normally do now i take that data (based upon the value in column H(which is our Vendo ID)), cut and paste each row that matches which vendor we're lookin for into new seperate files and save it. It is very tedious work. I' curious if it is possible to do this in a macro in which one would hav it so that it takes the data based upon the values in column H an makes seperate files for each different value with the data from eac corresponding row. I hope that makes sense -- DK ----------------------------------------------------------------------- DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451 View this thread: http://www.excelforum.com/showthread.php?threadid=39247 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving different files based upon data in particular column
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving different files based upon data in particular column
Hi DKY
See http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl "DKY" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving different files based upon data in particular column
Ron, I'm using your code titled 'Create a new workbook for all Uniqu values' it looks like it will do exactly what I want I just have t configure it and I'm having a difficult time. I'm supposed to change only this section, right? Code ------------------- FileFolder = "O:\Common\Common-Parts\INVTEAMS\TransUnder\SurplusVendors\" '<<< Change Set ws1 = ThisWorkbook.Sheets("Sheet1") '<<< Change 'Tip : Use a Dynamic range name, http://www.contextures.com/xlNames01.html#Dynamic 'or a fixed range like Range("A1:H1200") Set rng = ws1.Range("H2").CurrentRegion '<<< Chang ------------------- ~There aren't any other sections? ~Also, when I change Sheet1 to the actual name of my sheet it gives debug error. ~and I'm not sure how to do the range part. I picked H2 because that' the column I want to get the values from but will it get all th information behind it? Like in columns A:G -- DK ----------------------------------------------------------------------- DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451 View this thread: http://www.excelforum.com/showthread.php?threadid=39247 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving different files based upon data in particular column
Oops, I forgot to add the title. I'm using the code titled 'Create new workbook for all Unique values'. Thank -- DK ----------------------------------------------------------------------- DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451 View this thread: http://www.excelforum.com/showthread.php?threadid=39247 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving different files based upon data in particular column
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving different files based upon data in particular column
Look in the VBA help for SaveAs and read about FileFormat
FileFormat:=xlText -- Regards Ron de Bruin http://www.rondebruin.nl "DKY" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving different files based upon data in particular column
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving different files based upon data in particular column
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" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving different files based upon data in particular column
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" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving different files based upon data in particular column
Run-time error '13': Type mismatch then it highlights the following code. Code: -------------------- WBNew.SaveAs FileFolder & cell.Value & "_Surplus_" & FORMAT(Date, "yyyy-mmm-dd") & ".txt", FileFormat:=xlText _ , CreateBackup:=False -------------------- -- DKY ------------------------------------------------------------------------ DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515 View this thread: http://www.excelforum.com/showthread...hreadid=392476 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving different files based upon data in particular column
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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving different files based upon data in particular column
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" wrote in message ... 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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving different files based upon data in particular column
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" wrote in message ... 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 |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving different files based upon data in particular column
Instead of posting my code I just made a different macro for the tx files. So, now I have two. No big deal and I don't have to bother yo guys so much. LOL Thanks for the help. Ron de Bruin Wrote: 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 no possible Copy your whole macro in this thread then i change it for you -- Regards Ron de Bruin http://www.rondebruin.nl [/color -- DK ----------------------------------------------------------------------- DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451 View this thread: http://www.excelforum.com/showthread.php?threadid=39247 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Merge two files based on one column | Excel Discussion (Misc queries) | |||
| Excel saving pivot table and data as html files | Excel Discussion (Misc queries) | |||
| How do I avoid saving multiple data files for versioning purposes. | Excel Discussion (Misc queries) | |||
| Parse database into several files based on column D | Excel Programming | |||
| Data files are corrupt after saving from VBA - Excel 2000 | Excel Programming | |||
![DKY[_46_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
