0

I'm trying to create a VBA where I input information regarding payments across 22 columns. These columns include payment amount, account, etc. I can then run a macro and it formats the data to a usable string, then exports the data to a CSV file. The data has to be formatted with apostrophe's and commas between the info.

For example, column B is merchant ID and column C is merchant name (Column A is blank). I type 123 in column B and xyz in column C. It gets converted to '123','xyz', ... , ..., The goal is to have the formatting completed, concatenated and then exported to a CSV. Currently I'm using a mix of formulas/functions and a macro.

I use formulas to get to the formatting correct by using: ="'"&B2&"'"&"," Then use concatenate to merge all 22 rows to a single string

Then I recorded a macro with the following:

    Sub BCM_CSV_export()
'
' BCM_CSV_export Macro

    Range("B21").Select
    ActiveCell.FormulaR1C1 = "=""'""&R[-19]C&""'""&"","""
    Range("B21:V21").Select
    Selection.FillRight
    Range("B21:V30").Select
    ActiveWindow.SmallScroll Down:=9
    Selection.FillDown
    Range("B40").Select
    ActiveCell.FormulaR1C1 = "=CONCAT(R[-19]C:R[-19]C[20])"
    Range("B40:B49").Select
    Selection.FillDown
    Workbooks.Add
    Windows("BCM CSV Workbook.xlsm").Activate
    Selection.Copy
    Windows("Book1").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E13").Select
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs "CSVDate.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    ActiveWindow.Close
    Selection.ClearContents
    Range("B21").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-24

End Sub

The code seems to work on my laptop. The CSV file shows up in 'My Documents'. But my colleague encounters errors. The code doesnt seem to run.

My question is 2 fold: 1. Is there something inherently wrong with my macro that it only runs on my laptop? 2. Is there a way to convert the 22 rows of data into a single text string and into a new CSV workbook without the intermediate steps of my using the text string formatting and concatenate function?

I used the search function and didnt see anything exactly matching this. Thanks a lot everyone!

braX
  • 11,506
  • 5
  • 20
  • 33
bhar8670
  • 1
  • 1
    [`.Select`/`.Activate`/`Selection`/`ActiveCell`/`ActiveSheet`/`ActiveWorkbook` etc... should be avoided.](https://stackoverflow.com/a/10718179/1422451) – Parfait Apr 12 '20 at 04:22
  • refer [This](https://stackoverflow.com/questions/44783923/excel-macro-multiple-sheets-to-csv/44792295#44792295) – Dy.Lee Apr 12 '20 at 04:29
  • 1
    "my colleague encounters errors" - maybe ask them *what* errors they see? That could be useful. – Tim Williams Apr 12 '20 at 06:53

1 Answers1

0

You'd better to check the two lines of codes:

   Windows("BCM CSV Workbook.xlsm").Activate

   Windows("Book1").Activate

Maybe your colleague don't have the name of worrkbook BCM CSV Workbook.xlsm and Book1 opened

Anabas
  • 346
  • 1
  • 7