View Full Version : Exporting data from one excel to another
tutan
May 6, 2010, 01:35 AM
I have a excel workbook containing 20,000 rows of data. I will have to develop a macro to copy a certain range of data from that excel workbook & paste the same in another excel workbook, rename the workbook & save. Suppose one column contains certain rows with values as 1, next set of rows as value=2, another set of rows as values =3. I will have to take all the rows corresponding to value 1 and paste in the second excel workbook, rename, save && close. This process repeats for values=2 & 3. Please help in writing the Macro Code.
JBeaucaire
May 6, 2010, 07:55 AM
I call these Parse macros. On my website I've a page specifically for creating a separate workbook for each unique value in a key column of data.
The page is: Sheet1 to Workbooks (https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/parse-functions/sheet1-to-wbs)
There are many similar macros on this site.
tutan
May 7, 2010, 02:06 AM
I call these Parse macros. On my website I've a page specifically for creating a separate workbook for each unique value in a key column of data.
The page is: Sheet1 to Workbooks (https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/parse-functions/sheet1-to-wbs)
There are many similar macros on this site.
Hi JB,
Thanks for your help. I am a novice with macros.I still seem to have some problems with the code. I'm attaching the excel file for your reference. The filter needs to be placed in the column C ( where we have a set of rows based on this column as a primary key). Additionally I don't need to create separate worksheets. I have a template where this set of data needs to be placed from a particular range onwards. Can you please assist me with the merged code? Thanks for all your help
Cheers
Ani
JBeaucaire
May 7, 2010, 12:21 PM
1) You'll need to insert a row of titles at the top, in row1.
2) I can't seem to find the template you're referring to or any instructions on how the data would flow to that template.
Post with the template included and fill out the template manually with the first set of data from 4002756.
Also, what would these workbooks saved from this template be called? Those instructions would be needed, too. Perhaps saved as "code"+"date"
4002756 05-07-2010.xls
tutan
May 9, 2010, 09:24 AM
1) You'll need to insert a row of titles at the top, in row1.
2) I can't seem to find the template you're referring to or any instructions on how the data would flow to that template.
Post with the template included and fill out the template manually with the first set of data from 4002756.
Also, what would these workbooks saved from this template be called? Those instructions would be needed, too. Perhaps saved as "code"+"date"
4002756 05-07-2010.xls
Hi JB,
Hope you had a great weekend. You'r code to seggregate data and putting them in separate excel workbooks worked in my case. Thanks a lot. But I'm looking for additional functionalities. Let me answer your questions serialy:
1)I did'nt attach the template, as the size of the template is too large to attach. Just consider it as a template with lot of inbuilt Macro's. My Job is to put the seggregated data from the cell A20000 onwards for each distinct column value in the cell mentioned in my previous post. I will give you an example:
In the excel that I attached, column C contains repeated values. Like 4004207 is repeated in 6 rows. I need to put these 6 rows in the template which has a specific name (consider it anything.. It's hardcoded) from the location A20000 onwards. This is how data should flow.Your Macro currently is putting the data from the cell A1 onwards as it creates another spreadsheet.
2)You are almost there regarding renaming the template. I would like to add some more salt to it. It would be named as the following:
For Eg: 4004207_ANI_PR_DEN_05-07-2010.xls
Similarly for other distinct values in the first segment of the name.
I hope I could answer your questions. I can only say a big "THANK YOU" to you for all the help till now. Really appreciate it. Looking forward to your reply at the earliest.
Best Regards
Ani
tutan
May 11, 2010, 06:01 AM
Hi JB,
Hope you had a great weekend. You'r code to seggregate data and putting them in seperate excel workbooks worked in my case. Thanks a lot. But I'm looking for additional functionalities. Let me answer your questions serialy:
1)I did'nt attach the template, as the size of the template is too large to attach. Just consider it as a template with lot of inbuilt Macro's. My Job is to put the seggregated data from the cell A20000 onwards for each distinct column value in the cell mentioned in my previous post. I will give you an example:
In the excel that I attached, column C contains repeated values. Like 4004207 is repeated in 6 rows. I need to put these 6 rows in the template which has a specific name (consider it anything..It's hardcoded) from the location A20000 onwards. This is how data should flow.Your Macro currently is putting the data from the cell A1 onwards as it creates another spreadsheet.
2)You are almost there regarding renaming the template. I would like to add some more salt to it. It would be named as the following:
For Eg: 4004207_ANI_PR_DEN_05-07-2010.xls
Similarly for other distinct values in the first segment of the name.
I hope I could answer your questions. I can only say a big "THANK YOU" to you for all the help till now. Really appreciate it. Looking forward to your reply at the earliest.
Best Regards
Ani
Hi JB,
Any Inputs?
JBeaucaire
May 11, 2010, 08:10 AM
Take the macro I suggested and edit it for your working environment as best you can. Make notes in the code where you need things to act differently and post the resulting pseudo-code here, we'll complete it together.
tutan
May 14, 2010, 07:16 AM
Take the macro I suggested and edit it for your working environment as best you can. Make notes in the code where you need things to act differently and post the resulting pseudo-code here, we'll complete it together.
Hi JB,
I'm posting the code here. Thanks a lot for your prompt reply. The comments where I feel the changes needs to be done is commented in bold. Thanks a lot for your cooperation.
Sub Ani()
'Jerry Beaucaire (4/22/2010)
'Based on selected column, data is filtered to individual workbooks
'workbooks are named for the value plus today's date
Dim LR As Long, Itm As Long, MyCount As Long, vCol As Long
Dim was As Worksheet, MyArr As Variant, vTitles As String, SvPath As String
Application.ScreenUpdating = False
'Column to evaluate from, column A = 1, B = 2, etc.
vCol = 3
'Sheet with data in it
Set was = Sheets("Report1")
'Path to save files into, remember the final \
SvPath = "C:\2010\"
'Range where titles are across top of data, as string, data MUST
'have titles in this row, edit to suit your titles locale
vTitles = "A1:Z1"
'Spot bottom row of data
LR = was.Cells(ws.Rows.Count, vCol).End(xlUp).Row
'Get a temporary list of unique values from column A
was.Columns(vCol).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Range("EE1"), Unique:=True
'Sort the temporary list
was.Columns("EE:EE").Sort Key1:=ws.Range("EE2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
'Put list into an array for looping (values cannot be the result of formulas, must be constants)
MyArr = Application.WorksheetFunction.Transpose(ws.Range("EE2:EE" & Rows.Count).SpecialCells(xlCellTypeConstants))
'clear temporary worksheet list
was.Range("EE:EE").Clear
'Turn on the autofilter, one column only is all that is needed
was.Range(vTitles).AutoFilter
'Loop through list one value at a time
For Itm = 1 To UBound(MyArr)
was.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=MyArr(Itm)
was.Range("A1:A" & LR).EntireRow.Copy
Workbooks.Add */ Here I want to paste the data in a template with the name Anirban.xls */
Range("A1").PasteSpecial xlPasteAll
Cells.Columns.AutoFit
MyCount = MyCount + Range("A" & Rows.Count).End(xlUp).Row - 1
ActiveWorkbook.SaveAs SvPath & MyArr(Itm) & Format(Date, " MM-DD-YY"), xlNormal */ Here some part of the name should be hardcoded, rest is fine*/
ActiveWorkbook.Close False
was.Range(vTitles).AutoFilter Field:=vCol
Next Itm
'Cleanup
was.AutoFilterMode = False
MsgBox "Rows with data: " & (LR - 1) & vbLf & "Rows copied to other sheets: " & MyCount & vbLf & "Hope they match!!"
Application.ScreenUpdating = True
End Sub
JBeaucaire
May 14, 2010, 09:12 AM
1) The sub should be named for what it does. Naming it after yourself results in a less-than-obvious list of macros. ;)
2) Anirban.xls is a workbook name, not a worksheet name. We need full information regarding this.
- will this workbook already be open or does it need to be opened as we make each copy? (usually we need to open it)
- what sheet name in the workbook is the actual "template"?
- what is the target cell on that sheet to paste all the rows we copied? A20000?
3) 4004207_ANI_PR_DEN_05-07-2010.xls
Does the highlighted text come from the data anywhere or is that text hardcoded and will always be the same for all filenames?
ActiveWorkbook.SaveAs SvPath & MyArr(Itm) & _
"_ANI_PR_DEN_" & Format(Date, " MM-DD-YY"), xlNormal
ActiveWorkbook.Close False
tutan
May 16, 2010, 01:01 AM
1) The sub should be named for what it does. Naming it after yourself results in a less-than-obvious list of macros. ;)
2) Anirban.xls is a workbook name, not a worksheet name. We need full information regarding this.
- will this workbook already be open or does it need to be opened as we make each copy? (usually we need to open it)
- what sheet name in the workbook is the actual "template"?
- what is the target cell on that sheet to paste all the rows we copied? A20000?
3) 4004207_ANI_PR_DEN_05-07-2010.xls
Does the highlighted text come from the data anywhere or is that text hardcoded and will always be the same for all filenames?
ActiveWorkbook.SaveAs SvPath & MyArr(Itm) & _
"_ANI_PR_DEN_" & Format(Date, " MM-DD-YY"), xlNormal
ActiveWorkbook.Close False
Hi JB,
Thanks a lot for your time. I will answer your queries serially.
1)Yes. I agree to you regarding the naming of Sub. Forgive my Ignorance. I will take care of it.
2)a) Yes, Anirban.xls is a workbook name with only one worksheet.
b)We need to open the workbook as we make each copy, as you mentioned.
c)As there is only one worksheet, it's the template itself.
d) Yes, you are right , we need to paste it from A20000 onwards for all the unique rows we copy.
3) Yes, this text will be hard coded & will be same for all file names.
Kindly let me know if any more info is needed.
Thanks & Best Regards
Ani
JBeaucaire
May 16, 2010, 05:13 PM
Give this a try:
Option Explicit
Sub FilterToTemplateAndSave()
'Jerry Beaucaire (4/22/2010)
'updated (5/16/2010)
'Based on selected column, data is filtered to individual workbooks
'workbooks are named for the value plus today's date
'This macro assumes THIS workbook is the Data sheet to filter from
Dim LR As Long, Itm As Long, MyCount As Long, vCol As Long
Dim ws As Worksheet, MyArr As Variant
Dim vTitles As String, SvPath As String
Application.ScreenUpdating = False
'Column to evaluate from, column A = 1, B = 2, etc.
vCol = 3
'sheet with data in it to filter
Set ws = ThisWorkbook.Sheets("Report1")
'Path to save files into, remember the final \
SvPath = "C:\2010\"
'Range where titles are across top of data, as string, data MUST
'have titles in this row, edit to suit your titles locale
vTitles = "A1:Z1"
'Spot bottom row of data
LR = ws.Cells(ws.Rows.Count, vCol).End(xlUp).Row
'Get a temporary list of unique values from column A
ws.Columns(vCol).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range("EE1"), Unique:=True
'Sort the temporary list
ws.Columns("EE:EE").Sort Key1:=ws.Range("EE2"), _
Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
'Put list into an array for looping
'(values cannot be the result of formulas, must be constants)
MyArr = Application.WorksheetFunction.Transpose(ws.Range(" EE2:EE" & _
Rows.Count).SpecialCells(xlCellTypeConstants))
'clear temporary list of values
ws.Range("EE:EE").Clear
'Turn on the autofilter, one column only is all that is needed
ws.Range(vTitles).AutoFilter
'Loop through list one value at a time
For Itm = 1 To UBound(MyArr)
ws.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=MyArr(Itm)
ws.Range("A1:A" & LR).EntireRow.Copy
Workbooks.Open ("C:\2010\Anirban.xls")
Range("A20000").PasteSpecial xlPasteAll
Cells.Columns.AutoFit 'optional
MyCount = MyCount + Range("A" & Rows.Count).End(xlUp).Row - 1
ActiveWorkbook.SaveAs SvPath & MyArr(Itm) & _
"_ANI_PR_DEN_" & Format(Date, " MM-DD-YY"), xlNormal
ActiveWorkbook.Close False
ws.Range(vTitles).AutoFilter Field:=vCol
Next Itm
'Cleanup
ws.AutoFilterMode = False
MsgBox "Rows with data: " & (LR - 1) & vbLf & "Rows copied to other sheets: " _
& MyCount & vbLf & "Hope they match!!"
Application.ScreenUpdating = True
End Sub
tutan
May 17, 2010, 03:58 AM
Give this a try:
Option Explicit
Sub FilterToTemplateAndSave()
'Jerry Beaucaire (4/22/2010)
'updated (5/16/2010)
'Based on selected column, data is filtered to individual workbooks
'workbooks are named for the value plus today's date
'This macro assumes THIS workbook is the Data sheet to filter from
Dim LR As Long, Itm As Long, MyCount As Long, vCol As Long
Dim ws As Worksheet, MyArr As Variant
Dim vTitles As String, SvPath As String
Application.ScreenUpdating = False
'Column to evaluate from, column A = 1, B = 2, etc.
vCol = 3
'sheet with data in it to filter
Set ws = ThisWorkbook.Sheets("Report1")
'Path to save files into, remember the final \
SvPath = "C:\2010\"
'Range where titles are across top of data, as string, data MUST
'have titles in this row, edit to suit your titles locale
vTitles = "A1:Z1"
'Spot bottom row of data
LR = ws.Cells(ws.Rows.Count, vCol).End(xlUp).Row
'Get a temporary list of unique values from column A
ws.Columns(vCol).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range("EE1"), Unique:=True
'Sort the temporary list
ws.Columns("EE:EE").Sort Key1:=ws.Range("EE2"), _
Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
'Put list into an array for looping
'(values cannot be the result of formulas, must be constants)
MyArr = Application.WorksheetFunction.Transpose(ws.Range(" EE2:EE" & _
Rows.Count).SpecialCells(xlCellTypeConstants))
'clear temporary list of values
ws.Range("EE:EE").Clear
'Turn on the autofilter, one column only is all that is needed
ws.Range(vTitles).AutoFilter
'Loop through list one value at a time
For Itm = 1 To UBound(MyArr)
ws.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=MyArr(Itm)
ws.Range("A1:A" & LR).EntireRow.Copy
Workbooks.Open ("C:\2010\Anirban.xls")
Range("A20000").PasteSpecial xlPasteAll
Cells.Columns.AutoFit 'optional
MyCount = MyCount + Range("A" & Rows.Count).End(xlUp).Row - 1
ActiveWorkbook.SaveAs SvPath & MyArr(Itm) & _
"_ANI_PR_DEN_" & Format(Date, " MM-DD-YY"), xlNormal
ActiveWorkbook.Close False
ws.Range(vTitles).AutoFilter Field:=vCol
Next Itm
'Cleanup
ws.AutoFilterMode = False
MsgBox "Rows with data: " & (LR - 1) & vbLf & "Rows copied to other sheets: " _
& MyCount & vbLf & "Hope they match!!"
Application.ScreenUpdating = True
End Sub
Hi JB,
The Macro is working fine. Thanks a Lot for your help. It's sheer magic!!
Regards
Anirban
JBeaucaire
May 19, 2010, 07:54 PM
Glad to help.
TIP: Next time, resist using that QUOTE button for all your replies, notice how all cluttered all of your posts are compared to mine? Just use the "Answer This Question" box at the bottom of this page... much cleaner.
;)
JBeaucaire
May 19, 2010, 07:57 PM
By the way, that message box that appears at the end, is it EVER right? I'd think we needed to adjust the math a little to get it to be right. Maybe this:
Range("A20000").PasteSpecial xlPasteAll
Cells.Columns.AutoFit 'optional
MyCount = MyCount + Range("A" & Rows.Count).End(xlUp).Row - 20000
Or maybe - 19999?