Macro to copy/paste from one workbook to another
 

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

Macro to copy/paste from one workbook to another
 

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

Macro to copy/paste from one workbook to another
 

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
.

Macro to copy/paste from one workbook to another
 

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

Macro to copy/paste from one workbook to another
 

Thanks for your help Dave. This works great!

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

Macro to copy/paste from one workbook to another
 

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

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

Macro to copy/paste from one workbook to another
 

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
.

Macro to copy/paste from one workbook to another
 

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

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
.