Macro to copy/paste from one workbook to another

  • Thread starter Thread starter pm
  • Start date Start date
P

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
 
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
 
Dave - this works fabulously except I want the copy to go in cell A1 in the
CSV worksheet. Thanks.
 
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
Click to expand...

That means you could be overwriting some (maybe not all) of the existing data.
Does that matter?
 
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
 
If you have cells that contained formulas that evaluated to "", then those cells
are not blank. They only look blank.
with csvwks
Click to expand...
.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
Click to expand...

Then after this portion:
rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues
Click to expand...

'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
 
Thank you very much for your assistance.

Dave Peterson said:
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
Click to expand...
 
Back
Top