Prev: How do you lock a cell with data but still allow new data entry?
Next: Conditional Format with Name Range?
From: pm on 19 May 2010 16:09
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
From: Dave Peterson on 19 May 2010 16:23
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(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
From: pm on 19 May 2010 17:38
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(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
> .
>
From: Dave Peterson on 19 May 2010 18:26
> 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(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
> > .
> >

--

Dave Peterson
From: pm on 20 May 2010 10:30
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(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
> > > .
> > >
>
> --
>
> Dave Peterson
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: How do you lock a cell with data but still allow new data entry?
Next: Conditional Format with Name Range?