Delete rows from another tab based on data in cells and keep formula

[ScottGFrustrated]

I have a simple workbook that I use to enter data and I have macros that send the data to another workbook. The workbook I am sending to must be CSV and the problem I am having is if I only have data in my top row, when it transfers to new workbook, I am getting commas in rows below that. Currently, to bypass this issue, I have to open the CSV workbook, manually select the rows below the data that I need and delete them every time I transfer. I want to be able to click my "Export Data" button and transfer data to the new workbook and ALSO delete any row that contains "<DO NOT CHANGE" in column B.

The data I enter in Worksheet 'Data' automatically transposes to Worksheet 2 'Transfer' and that is where the information for the transfer comes from. I don't want to delete from Worksheet 2 because I have formulas there.

Workbook I enter data in - SFC Transfers
Worksheet it is on - Data
Worksheet 2 - Transfer
Workbook it transfers to - Manualtransfers.csv

My current Macro:
Public Sub XPORTCSV()

Dim wbkExport As Workbook
Dim shtToExport As Worksheet
Dim FPath As String
Dim answer As Integer
Dim RowCount As Long

RowCount = Worksheets("System Settings").Range("A87").Value


Sheets("Transfer").Select
Range("A1:J7").Select
Selection.Copy


FPath = Sheets("System Settings").Range("C2").Text

Set shtToExport = ThisWorkbook.Worksheets("Transfer")
Set wbkExport = Application.Workbooks.Add
shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
Application.DisplayAlerts = False
wbkExport.SaveAs Filename:=FPath & "\ManualTransfers", FileFormat:=xlCSV
Application.DisplayAlerts = True
wbkExport.Close SaveChanges:=False




End Sub

 

[rpaulson]

I never save an Workbook as a csv, I always export them to a csv. See Below.

Sub Export()
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Transfer")

fpath = Sheets("System Settings").Range("C2").Text
fName =  "\" & ws.Name & ".csv"

delim = ","
lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column
For r = 2 To ws.Cells(Rows.Count, "A").End(xlUp).Row 'loop thru every row

For C = 1 To lc 'loop thru every column

    Data = Data & ws.Cells(r, C) & delim

Next C
Data = Data & vbCrLf

Next r



Open fpath & fName For Output As #1
Print #1, Data
Close #1
Data = ""

End Sub

hth,
-Ross