Macro to copy/paste from one workbook to another
Thank you very much for your assistance.
"Dave Peterson" wrote:
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
.
|