Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
write a copy/paste from one workbook to another in a macro | Excel Discussion (Misc queries) | |||
Macro to Copy / paste from 1 workbook to another | Excel Discussion (Misc queries) | |||
macro to copy and paste into another workbook | Excel Discussion (Misc queries) | |||
Macro to open workbook and copy and paste values in to orig workbo | Excel Worksheet Functions | |||
I cannot paste from one workbook to another. Copy works, paste do. | Excel Discussion (Misc queries) |