Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying, Pasting and Saving as (delimted.txt) on seperate sheets
Hi,
I have a spreadsheet which general information, that I need to cut and paste into another workbook and save as a delimited txt file. The current spreadsheet I am working on contains 8 columns. In cell D, I have a series of numbers that Cells E, F, G and H link to. - Im not really interested in Columns A-C. Cell D may contain anything from 1 row to 100+ rows of the same number. I need to filter on a particular number (if I put the filter application on it shows me each unique number) and once filtered I need to copy and past the contents of Cells D, E, F, G and H to another workbook and save this as a "Text (tab delimited) (*txt)". To do this manually is a right pain in the rear as the spreadsheet is approx. 11652 rows, which is ever growing. I wanted to know if there is a way I can write/create a macro for this spreadsheet, so we can run it on a weekly basis if any more information gets added. Your help would be much appreciated! Thanks T-bone! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying, Pasting and Saving as (delimted.txt) on seperate sheets
This expects headers in D1.
Then it does an advanced filter to show the unique entries (data|filter|advancedfilter in xl2003 menus). Then it keeps track of those visible cells and applies data|filter|autofilter to column D for each one of those unique entries. It saves the files using each unique value--Hopefully, they won't be invalid filenames! And stores them in C:\temp. Make sure the output folder exists before you test it. Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim myUniques As Range Dim VRng As Range Dim wks As Worksheet Dim tempWks As Worksheet Set wks = Worksheets("sheet1") With wks 'remove any existing autofilter .AutoFilterMode = False Set myRng = .Range("D1", .Cells(.Rows.Count, "D").End(xlUp)) With myRng .AdvancedFilter action:=xlFilterInPlace, unique:=True Set myUniques = Nothing On Error Resume Next 'come down one row to avoid the header Set myUniques = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) On Error GoTo 0 If myUniques Is Nothing Then MsgBox "Nothing under D1!" Exit Sub End If For Each myCell In myUniques.Cells .AutoFilter field:=1, Criteria1:=myCell.Value Set VRng = Nothing On Error Resume Next 'come down one row, but include 5 columns! Set VRng = .Resize(.Rows.Count - 1, 5).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) On Error GoTo 0 If VRng Is Nothing Then MsgBox "something bad happened with: " & myCell.Value Exit Sub End If Set tempWks = Workbooks.Add(1).Worksheets(1) VRng.Copy _ Destination:=tempWks.Range("A1") With tempWks.Parent Application.DisplayAlerts = False .SaveAs Filename:="C:\temp\" & myCell.Value & ".txt", _ FileFormat:=xlText Application.DisplayAlerts = True .Close savechanges:=False End With Next myCell End With .AutoFilterMode = False End With End Sub T-bone wrote: Hi, I have a spreadsheet which general information, that I need to cut and paste into another workbook and save as a delimited txt file. The current spreadsheet I am working on contains 8 columns. In cell D, I have a series of numbers that Cells E, F, G and H link to. - Im not really interested in Columns A-C. Cell D may contain anything from 1 row to 100+ rows of the same number. I need to filter on a particular number (if I put the filter application on it shows me each unique number) and once filtered I need to copy and past the contents of Cells D, E, F, G and H to another workbook and save this as a "Text (tab delimited) (*txt)". To do this manually is a right pain in the rear as the spreadsheet is approx. 11652 rows, which is ever growing. I wanted to know if there is a way I can write/create a macro for this spreadsheet, so we can run it on a weekly basis if any more information gets added. Your help would be much appreciated! Thanks T-bone! -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying, Pasting and Saving as (delimted.txt) on seperate shee
Hi Dave,
Do you think it would be ok to send you my spreadhseet - I can't seem to get the macro to work and I'm unfortunatley not tecnically minded :-( Thanks T-bone "Dave Peterson" wrote: This expects headers in D1. Then it does an advanced filter to show the unique entries (data|filter|advancedfilter in xl2003 menus). Then it keeps track of those visible cells and applies data|filter|autofilter to column D for each one of those unique entries. It saves the files using each unique value--Hopefully, they won't be invalid filenames! And stores them in C:\temp. Make sure the output folder exists before you test it. Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim myUniques As Range Dim VRng As Range Dim wks As Worksheet Dim tempWks As Worksheet Set wks = Worksheets("sheet1") With wks 'remove any existing autofilter .AutoFilterMode = False Set myRng = .Range("D1", .Cells(.Rows.Count, "D").End(xlUp)) With myRng .AdvancedFilter action:=xlFilterInPlace, unique:=True Set myUniques = Nothing On Error Resume Next 'come down one row to avoid the header Set myUniques = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) On Error GoTo 0 If myUniques Is Nothing Then MsgBox "Nothing under D1!" Exit Sub End If For Each myCell In myUniques.Cells .AutoFilter field:=1, Criteria1:=myCell.Value Set VRng = Nothing On Error Resume Next 'come down one row, but include 5 columns! Set VRng = .Resize(.Rows.Count - 1, 5).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) On Error GoTo 0 If VRng Is Nothing Then MsgBox "something bad happened with: " & myCell.Value Exit Sub End If Set tempWks = Workbooks.Add(1).Worksheets(1) VRng.Copy _ Destination:=tempWks.Range("A1") With tempWks.Parent Application.DisplayAlerts = False .SaveAs Filename:="C:\temp\" & myCell.Value & ".txt", _ FileFormat:=xlText Application.DisplayAlerts = True .Close savechanges:=False End With Next myCell End With .AutoFilterMode = False End With End Sub T-bone wrote: Hi, I have a spreadsheet which general information, that I need to cut and paste into another workbook and save as a delimited txt file. The current spreadsheet I am working on contains 8 columns. In cell D, I have a series of numbers that Cells E, F, G and H link to. - Im not really interested in Columns A-C. Cell D may contain anything from 1 row to 100+ rows of the same number. I need to filter on a particular number (if I put the filter application on it shows me each unique number) and once filtered I need to copy and past the contents of Cells D, E, F, G and H to another workbook and save this as a "Text (tab delimited) (*txt)". To do this manually is a right pain in the rear as the spreadsheet is approx. 11652 rows, which is ever growing. I wanted to know if there is a way I can write/create a macro for this spreadsheet, so we can run it on a weekly basis if any more information gets added. Your help would be much appreciated! Thanks T-bone! -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying, Pasting and Saving as (delimted.txt) on seperate shee
No thanks.
Describe your problems in plain text and post it in this thread. You'll have lots of potential helpers. T-bone wrote: Hi Dave, Do you think it would be ok to send you my spreadhseet - I can't seem to get the macro to work and I'm unfortunatley not tecnically minded :-( Thanks T-bone "Dave Peterson" wrote: This expects headers in D1. Then it does an advanced filter to show the unique entries (data|filter|advancedfilter in xl2003 menus). Then it keeps track of those visible cells and applies data|filter|autofilter to column D for each one of those unique entries. It saves the files using each unique value--Hopefully, they won't be invalid filenames! And stores them in C:\temp. Make sure the output folder exists before you test it. Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim myUniques As Range Dim VRng As Range Dim wks As Worksheet Dim tempWks As Worksheet Set wks = Worksheets("sheet1") With wks 'remove any existing autofilter .AutoFilterMode = False Set myRng = .Range("D1", .Cells(.Rows.Count, "D").End(xlUp)) With myRng .AdvancedFilter action:=xlFilterInPlace, unique:=True Set myUniques = Nothing On Error Resume Next 'come down one row to avoid the header Set myUniques = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) On Error GoTo 0 If myUniques Is Nothing Then MsgBox "Nothing under D1!" Exit Sub End If For Each myCell In myUniques.Cells .AutoFilter field:=1, Criteria1:=myCell.Value Set VRng = Nothing On Error Resume Next 'come down one row, but include 5 columns! Set VRng = .Resize(.Rows.Count - 1, 5).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) On Error GoTo 0 If VRng Is Nothing Then MsgBox "something bad happened with: " & myCell.Value Exit Sub End If Set tempWks = Workbooks.Add(1).Worksheets(1) VRng.Copy _ Destination:=tempWks.Range("A1") With tempWks.Parent Application.DisplayAlerts = False .SaveAs Filename:="C:\temp\" & myCell.Value & ".txt", _ FileFormat:=xlText Application.DisplayAlerts = True .Close savechanges:=False End With Next myCell End With .AutoFilterMode = False End With End Sub T-bone wrote: Hi, I have a spreadsheet which general information, that I need to cut and paste into another workbook and save as a delimited txt file. The current spreadsheet I am working on contains 8 columns. In cell D, I have a series of numbers that Cells E, F, G and H link to. - Im not really interested in Columns A-C. Cell D may contain anything from 1 row to 100+ rows of the same number. I need to filter on a particular number (if I put the filter application on it shows me each unique number) and once filtered I need to copy and past the contents of Cells D, E, F, G and H to another workbook and save this as a "Text (tab delimited) (*txt)". To do this manually is a right pain in the rear as the spreadsheet is approx. 11652 rows, which is ever growing. I wanted to know if there is a way I can write/create a macro for this spreadsheet, so we can run it on a weekly basis if any more information gets added. Your help would be much appreciated! Thanks T-bone! -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying, Pasting and Saving as (delimted.txt) on seperate shee
I got it to work! Thanks so much for your time and help! Much appreciated!
;o) "Dave Peterson" wrote: No thanks. Describe your problems in plain text and post it in this thread. You'll have lots of potential helpers. T-bone wrote: Hi Dave, Do you think it would be ok to send you my spreadhseet - I can't seem to get the macro to work and I'm unfortunatley not tecnically minded :-( Thanks T-bone "Dave Peterson" wrote: This expects headers in D1. Then it does an advanced filter to show the unique entries (data|filter|advancedfilter in xl2003 menus). Then it keeps track of those visible cells and applies data|filter|autofilter to column D for each one of those unique entries. It saves the files using each unique value--Hopefully, they won't be invalid filenames! And stores them in C:\temp. Make sure the output folder exists before you test it. Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim myUniques As Range Dim VRng As Range Dim wks As Worksheet Dim tempWks As Worksheet Set wks = Worksheets("sheet1") With wks 'remove any existing autofilter .AutoFilterMode = False Set myRng = .Range("D1", .Cells(.Rows.Count, "D").End(xlUp)) With myRng .AdvancedFilter action:=xlFilterInPlace, unique:=True Set myUniques = Nothing On Error Resume Next 'come down one row to avoid the header Set myUniques = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) On Error GoTo 0 If myUniques Is Nothing Then MsgBox "Nothing under D1!" Exit Sub End If For Each myCell In myUniques.Cells .AutoFilter field:=1, Criteria1:=myCell.Value Set VRng = Nothing On Error Resume Next 'come down one row, but include 5 columns! Set VRng = .Resize(.Rows.Count - 1, 5).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) On Error GoTo 0 If VRng Is Nothing Then MsgBox "something bad happened with: " & myCell.Value Exit Sub End If Set tempWks = Workbooks.Add(1).Worksheets(1) VRng.Copy _ Destination:=tempWks.Range("A1") With tempWks.Parent Application.DisplayAlerts = False .SaveAs Filename:="C:\temp\" & myCell.Value & ".txt", _ FileFormat:=xlText Application.DisplayAlerts = True .Close savechanges:=False End With Next myCell End With .AutoFilterMode = False End With End Sub T-bone wrote: Hi, I have a spreadsheet which general information, that I need to cut and paste into another workbook and save as a delimited txt file. The current spreadsheet I am working on contains 8 columns. In cell D, I have a series of numbers that Cells E, F, G and H link to. - Im not really interested in Columns A-C. Cell D may contain anything from 1 row to 100+ rows of the same number. I need to filter on a particular number (if I put the filter application on it shows me each unique number) and once filtered I need to copy and past the contents of Cells D, E, F, G and H to another workbook and save this as a "Text (tab delimited) (*txt)". To do this manually is a right pain in the rear as the spreadsheet is approx. 11652 rows, which is ever growing. I wanted to know if there is a way I can write/create a macro for this spreadsheet, so we can run it on a weekly basis if any more information gets added. Your help would be much appreciated! Thanks T-bone! -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying, Pasting and Saving as (delimted.txt) on seperate shee
Glad you got it working!
T-bone wrote: I got it to work! Thanks so much for your time and help! Much appreciated! ;o) "Dave Peterson" wrote: No thanks. Describe your problems in plain text and post it in this thread. You'll have lots of potential helpers. T-bone wrote: Hi Dave, Do you think it would be ok to send you my spreadhseet - I can't seem to get the macro to work and I'm unfortunatley not tecnically minded :-( Thanks T-bone "Dave Peterson" wrote: This expects headers in D1. Then it does an advanced filter to show the unique entries (data|filter|advancedfilter in xl2003 menus). Then it keeps track of those visible cells and applies data|filter|autofilter to column D for each one of those unique entries. It saves the files using each unique value--Hopefully, they won't be invalid filenames! And stores them in C:\temp. Make sure the output folder exists before you test it. Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim myUniques As Range Dim VRng As Range Dim wks As Worksheet Dim tempWks As Worksheet Set wks = Worksheets("sheet1") With wks 'remove any existing autofilter .AutoFilterMode = False Set myRng = .Range("D1", .Cells(.Rows.Count, "D").End(xlUp)) With myRng .AdvancedFilter action:=xlFilterInPlace, unique:=True Set myUniques = Nothing On Error Resume Next 'come down one row to avoid the header Set myUniques = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) On Error GoTo 0 If myUniques Is Nothing Then MsgBox "Nothing under D1!" Exit Sub End If For Each myCell In myUniques.Cells .AutoFilter field:=1, Criteria1:=myCell.Value Set VRng = Nothing On Error Resume Next 'come down one row, but include 5 columns! Set VRng = .Resize(.Rows.Count - 1, 5).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) On Error GoTo 0 If VRng Is Nothing Then MsgBox "something bad happened with: " & myCell.Value Exit Sub End If Set tempWks = Workbooks.Add(1).Worksheets(1) VRng.Copy _ Destination:=tempWks.Range("A1") With tempWks.Parent Application.DisplayAlerts = False .SaveAs Filename:="C:\temp\" & myCell.Value & ".txt", _ FileFormat:=xlText Application.DisplayAlerts = True .Close savechanges:=False End With Next myCell End With .AutoFilterMode = False End With End Sub T-bone wrote: Hi, I have a spreadsheet which general information, that I need to cut and paste into another workbook and save as a delimited txt file. The current spreadsheet I am working on contains 8 columns. In cell D, I have a series of numbers that Cells E, F, G and H link to. - Im not really interested in Columns A-C. Cell D may contain anything from 1 row to 100+ rows of the same number. I need to filter on a particular number (if I put the filter application on it shows me each unique number) and once filtered I need to copy and past the contents of Cells D, E, F, G and H to another workbook and save this as a "Text (tab delimited) (*txt)". To do this manually is a right pain in the rear as the spreadsheet is approx. 11652 rows, which is ever growing. I wanted to know if there is a way I can write/create a macro for this spreadsheet, so we can run it on a weekly basis if any more information gets added. Your help would be much appreciated! Thanks T-bone! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add two cells from seperate work sheets into a cell on seperate wo | Excel Worksheet Functions | |||
Copying store numbers and pasting them into a seperate workbook | Excel Discussion (Misc queries) | |||
HELP: Copying and pasting to Sheets... | Excel Programming | |||
HELP: Copying and pasting to Sheets... | Excel Programming | |||
Problem copying range and pasting to multiple sheets | Excel Programming |