VB PasteSpecial when range changes

  • Thread starter Thread starter mjwillyone
  • Start date Start date
M

mjwillyone

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.
 
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 path>List.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 path>List.txt", _
FileFormat:=xlText
End Sub
 
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
 
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
 
Back
Top