Prev: How do you lock a cell with data but still allow new data entry?
Next: Conditional Format with Name Range?
From: pm on 20 May 2010 10:31
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(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:54
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" 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
> .
>
From: Dave Peterson on 20 May 2010 11:52
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
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(xlCellTypeBlanks).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(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
> > .
> >

--

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