Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB PasteSpecial when range changes
Dear Friends,
I have a list of names in column A that changes each time I run a concatenate macro I have created. The number of different names is never the same. I would like a way to copy the complete list of names and paste-special then to column B. I want to paste-special I do not want the concatenate formula to copy, only the values. Then I need to delete column A. Next, I need a way to export to a simple text file (called Name List.txt) the entire list of names. Thank you in advance for your help. --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB PasteSpecial when range changes
Did you try recording a macro - select column A, copy, paste special
to B, delete A, save as .txt? This is what I get: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 12/27/2003 by J.E. McGimpsey ' ' Columns("A:A").Select Selection.Copy Columns("B:B").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("A:A").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ChDir "<my path" Columns("A:A").Select ActiveWorkbook.SaveAs Filename:= _ "<my pathList.txt", FileFormat:=xlText, _ CreateBackup:=False End Sub It doesn't take a lot of work to clean it up: Public Sub Macro1() Columns("A:A").Copy Columns("B:B").PasteSpecial Paste:=xlValues Columns("A:A").Delete Shift:=xltoLeft ActiveWorkbook.SaveAs _ Filename:="<my pathList.txt", _ FileFormat:=xlText End Sub In article , mjwillyone wrote: Dear Friends, I have a list of names in column A that changes each time I run a concatenate macro I have created. The number of different names is never the same. I would like a way to copy the complete list of names and paste-special then to column B. I want to paste-special I do not want the concatenate formula to copy, only the values. Then I need to delete column A. Next, I need a way to export to a simple text file (called Name List.txt) the entire list of names. Thank you in advance for your help. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB PasteSpecial when range changes
Mr. McGimpsey,
Thank you so much for your help. The macro worked great! I have one question in relation to the output file format. Is there a way that the text file can display the separate lines of text without the parenthesis? "Stephen Jones,227,44525,123567892" "Ronald Jameson,668.33,48592,885246598" etc . . . (Note that while the above text "fields' are separated by commas, that is exactly how I have "programmed" it to do.) Thank you very much! Mike --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB PasteSpecial when range changes
If by "parentheses" you mean the double-quotes, see:
http://www.mcgimpsey.com/excel/textf...ml#csvnoquotes In article , mjwillyone wrote: Mr. McGimpsey, Thank you so much for your help. The macro worked great! I have one question in relation to the output file format. Is there a way that the text file can display the separate lines of text without the parenthesis? "Stephen Jones,227,44525,123567892" "Ronald Jameson,668.33,48592,885246598" etc . . . (Note that while the above text "fields' are separated by commas, that is exactly how I have "programmed" it to do.) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB PasteSpecial when range changes
Mr. McGimpsey,
Thanks for the helpful link. I am sorry . . . I meant to ask how to remove the quotation marks, not parenthesis. Good think you caught my mistake. I must be spending too many hours on these VB routines! Thank you again, Mike --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PasteSpecial / Formats also pastes a random range name | Excel Discussion (Misc queries) | |||
vba pastespecial | Excel Programming | |||
PasteSpecial problem | Excel Programming | |||
A More Effective Way of Using PasteSpecial? | Excel Programming | |||
pastespecial | Excel Programming |