Looking for a wee bit of help with VBA

[NoviceSeekingHelp]

Hi While having a reasonable understaning of excel, when it comes to VBA, I am a complete novice. I am attempting to use a macro to do what I hoped was going to be a simple task, but have run up against a stumbling block which I am sure one the of the guru's here will be able to solve in less than 5 seconds without raising a sweat. The problem I have is, I am using this very very basic code to export data from an excel sheet to a csv file Sub ExportTransactionsIntoACSVFile() ' ExportTransactionsIntoACSVFile Macro Sheets("Export").Select Range("A1:M43").Select Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=True, Transpose:=False Application.CutCopyMode = False ActiveWorkbook.SaveAs Filename:= _ "D:\ExportFile.csv", _ FileFormat:=xlCSV, CreateBackup:=False ActiveWindow.Close Sheets("Sheet1").Select Range("F8").Select End Sub In a neolithic way, it does the job, however, what I would like to do is export only the dynamically generated rows that contain values, not the range A1:M43. Is there a genius out there who can edit my macro to ensure the code only exports the rows that contain data, (not rows that contain formulas)? Huge thanks in advance.

 

[jproffer]

I think I got it split up right. When you post code, please use the code tags like this, but with no spaces:

[ code ]
...your code here
[ / code ]

anyhow, now what do you want to do?

Sub ExportTransactionsIntoACSVFile()
' ExportTransactionsIntoACSVFile Macro
Sheets("Export").Select
Range("A1:M43").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="D:\ExportFile.csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWindow.Close
Sheets("Sheet1").Select
Range("F8").Select
End Sub

 

[TMShucks]

Maybe:

[COLOR=#00007f]Sub[/COLOR] ExportTransactionsIntoACSVFile()
[COLOR=#007f00]' ExportTransactionsIntoACSVFile Macro[/COLOR]
Sheets("Export").Select
Range("A1:M43").SpecialCells(xlCellTypeConstants, 23).Copy
 
Workbooks.Add
Selection.PasteSpecial _
    Paste:=xlPasteValuesAndNumberFormats, _
    Operation:=xlNone, _
    SkipBlanks:=True, _
    Transpose:=[COLOR=#00007f]False[/COLOR]
Application.CutCopyMode = [COLOR=#00007f]False[/COLOR]
ActiveWorkbook.SaveAs _
    Filename:="M:\ExportFile.csv", _
    FileFormat:=xlCSV, _
    CreateBackup:=[COLOR=#00007f]False[/COLOR]
ActiveWindow.Close
Sheets("Sheet1").Select
Range("F8").Select
[COLOR=#00007f]End[/COLOR] [COLOR=#00007f]Sub[/COLOR]

 

[NoviceSeekingHelp]

Hi TMShucks

Thanks for your help and appreicate the formatting tips (though cannot get the code indented correctly) and fast response.

However, when I run the code, it throws an error; Runtime error. That command cannot be used on multiple selections.
The line it highlights is;

Range("A1:M43").SpecialCells(xlCellTypeConstants, 23).Copy
[code]

[code]
Sub ExportTransactionsIntoACSVFile()
' ExportTransactionsIntoACSVFile Macro
Sheets("Export").Select
Range("A1:M43").SpecialCells(xlCellTypeConstants, 23).Copy
 
Workbooks.Add
Selection.PasteSpecial _
  Paste:=xlPasteValuesAndNumberFormats, _
  Operation:=xlNone, _
  SkipBlanks:=True, _
  Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs _
  Filename:="M:\ExportFile.csv", _
  FileFormat:=xlCSV, _
  CreateBackup:=False
ActiveWindow.Close
Sheets("Sheet1").Select
Range("F8").Select
End Sub
[code]

 

[TMShucks]

To get the formatting, you need CODE in square brackets, followed by your code and then \CODE in square brackets at the end.

What does your actual data and spreadsheet actually look like? Which cells, rows, columns have data (constants) in them and which ones have formulae?

The test was done with a single block of constant data in the middle of a block of formulae. Looks like it will be necessary to locate the fixed data and copy it block by block.

 

[NoviceSeekingHelp]

Sub ExportTransactionsIntoACSVFile()
' ExportTransactionsIntoACSVFile Macro
Sheets("Export").Select
Range("A1:M43").SpecialCells(xlCellTypeConstants, 23).Copy
 
Workbooks.Add
Selection.PasteSpecial _
    Paste:=xlPasteValuesAndNumberFormats, _
    Operation:=xlNone, _
    SkipBlanks:=True, _
    Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs _
    Filename:="M:\ExportFile.csv", _
    FileFormat:=xlCSV, _
    CreateBackup:=False
ActiveWindow.Close
Sheets("Sheet1").Select
Range("F8").Select
End Sub
[\CODE]

The data is a number of dynamically added rows of bank transactions for daily exporting and uploading in a predetermined file format to an internet banking website.
The rows data starts at A1 and across to Col M. Depending on the number of daily transactions will depened on the number of rows to export. Currently, I select rows 1 to 43, but as these are not always containing row data, I am forced to manually edit the export file and removed ',' seperators for the non data rows. For example, Rows 1 to 10 may contain data and the macro record correctly rows 1 to 10, however "," for rows 11 to 43 Columns A:M.
If it is an easy solution and you could help, it would be great. If it is going to take longer  - please give it a miss. I don't want to waste anyones valuable time here.
Cheers

 

[NoviceSeekingHelp]

Oh and forgot to add. Columns A:M down to row 43 have formulas in them.

 

[GTO]

...\CODE in square...

That looks like something I'd do.

What TMS meant was the closing tag needs a forward slash (ie: Solidus).

[code=rich]
...your code here
[/code]

 

[TMShucks]

Oops, did I say that? You know, I was sure it didn't look quite right ... I'm just used to adding the tags with the # icon.

In another forum in another galaxy far, far away, I'd go back and fix them all to salve my embarrassment ... but I can't do that here.