If you have cells that contained formulas that evaluated to "", then those cells
are not blank. They only look blank.

with csvwks
.cells.clear '<-- clear any existing data
'where should it go in the CSV worksheet?
'I put it after the last used cell in column A
set destcell = .Range("A1")
end with

Then after this portion:

rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues

'Add a few more lines to clean up those cells that contained "":

with destcell.entirecolumn
.cells.Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.cells.Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False

on error resume next 'just in case there are no empty cells
.cells.specialcells(xlcelltypeblanks).entirerow.de lete
on error goto 0
End With







pm wrote:

Dave, At the end of the macro I want to delete in the CSV file the rows that
are blank - but neither of these work - I'm using:

ActiveSheet.Range("A2:A50").SpecialCells(xlCellTyp eBlanks).EntireRow.Delete
ActiveWorkbook.Save

or

Set r = Range("A3:A200")
Set rr = r.SpecialCells(xlCellTypeBlanks)
rr.EntireRow.Delete

"Dave Peterson" wrote:

with csvwks
'where should it go in the CSV worksheet?
'I put it after the last used cell in column A
set destcell = .Range("A1")
end with

That means you could be overwriting some (maybe not all) of the existing data.
Does that matter?

pm wrote:

Dave - this works fabulously except I want the copy to go in cell A1 in the
CSV worksheet. Thanks.

"Dave Peterson" wrote:

Dim InvWks as worksheet
dim CSVWks as worksheet
Dim LastRow as long
dim LastCol as long
dim RngToCopy as range
Dim DestCell as range

set InvWks = worksheets("inv_load to Lawson")
set csvwks = Workbooks.Open _
(Filename:="X:\Legal to Accounting Check Requests\Sales Tax\" _
& "slsTax dbapcvi.csv").worksheets(1)

with invwks
lastrow = .cells(.rows.count,"A").end(xlup).row
lastcol = .cells(1,.columns.count).end(xltoleft).column

set rngtocopy = .range("A1", .cells(lastrow, lastcol))
end with

with csvwks
'where should it go in the CSV worksheet?
'I put it after the last used cell in column A
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues



pm wrote:

My original macro looked like this:

Sheets("Inv_Load to Lawson").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Open Filename:= _
"X:\Legal to Accounting Check Requests\Sales Tax\slsTax dbapcvi.csv"

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Then someone suggested I try this one - but neither of them work......I am
trying to copy a range of data from one file to another...should be simple
but I can't get it to work!?!?

Sheets("Inv_Load to
Lawson").Select.Range("A1").End(xlToRight).End(xlD own).Copy
Workbooks.Open Filename:= _
"X:\Legal to Accounting Check Requests\Sales Tax\slsTax dbapcvi.csv"

ActiveSheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

--

Dave Peterson
.


--

Dave Peterson
.


--

Dave Peterson