Macro to copy/paste from one workbook to another

[pm]

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(xlDown).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]

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]

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

 

[Dave Peterson]

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]

Thanks for your help Dave. This works great!

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

 

[pm]

I actually want the data to be overwritten in the CSV file.....so that's
perfect. Thanks.

 

[pm]

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(xlCellTypeBlanks).EntireRow.Delete
ActiveWorkbook.Save

or

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

 

[Dave Peterson]

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

 

[pm]

Thank you very much for your assistance.

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

.cells.clear '<-- clear any existing data

Then after this portion:


'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.delete