ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   VB PasteSpecial when range changes (https://www.excelbanter.com/excel-programming/286291-vbulletin-pastespecial-when-range-changes.html)

mjwillyone[_2_]

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/


J.E. McGimpsey

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/


mjwillyone[_5_]

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/


J.E. McGimpsey

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.)


mjwillyone[_6_]

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/



All times are GMT +1. The time now is 11:57 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com