Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to transfer data from one workbook to another with VB
here is the scenario;
I have a blank master invoice file which is opened and filled filled out when a customer makes a purchase. things like customer name information, product information, prices and totals. I have an existing command on the worksheet button that prints and saves the file. what i want to do is add to that command button VB code which will gather customer information, tax, subtotals and add that to a master ledger sheet in a seperate workbook at the next availible row starting in column A. i have some glimmerings on where to start but not enough to get going. anyone got an idea how to make this work? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to transfer data from one workbook to another with VB
FName = "c:\temp\book1.xls"
set master = workbooks.open(FName) with master.sheets("Sheet1") LastRow = .Range("A" & rows.Count).end(xlup).Row NewRow = LastRow + 1 end with with Thisworkbook.Sheets("Sheet1") Tax = .Range("A20") with master.sheets("Sheet1") .Range("A" & NewRow) = Tax end with end with master.close savechanges:=true "Dan Worley" wrote: here is the scenario; I have a blank master invoice file which is opened and filled filled out when a customer makes a purchase. things like customer name information, product information, prices and totals. I have an existing command on the worksheet button that prints and saves the file. what i want to do is add to that command button VB code which will gather customer information, tax, subtotals and add that to a master ledger sheet in a seperate workbook at the next availible row starting in column A. i have some glimmerings on where to start but not enough to get going. anyone got an idea how to make this work? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to transfer data from one workbook to another with VB
Joe,
What is you have path of the workbook to copy from in cell A1, and in cell A2 the path of the workbook to copy to, and assume both workbooks are formatted the same, its just one has data the other is blank. Thank you, Adnan "Joel" wrote: FName = "c:\temp\book1.xls" set master = workbooks.open(FName) with master.sheets("Sheet1") LastRow = .Range("A" & rows.Count).end(xlup).Row NewRow = LastRow + 1 end with with Thisworkbook.Sheets("Sheet1") Tax = .Range("A20") with master.sheets("Sheet1") .Range("A" & NewRow) = Tax end with end with master.close savechanges:=true "Dan Worley" wrote: here is the scenario; I have a blank master invoice file which is opened and filled filled out when a customer makes a purchase. things like customer name information, product information, prices and totals. I have an existing command on the worksheet button that prints and saves the file. what i want to do is add to that command button VB code which will gather customer information, tax, subtotals and add that to a master ledger sheet in a seperate workbook at the next availible row starting in column A. i have some glimmerings on where to start but not enough to get going. anyone got an idea how to make this work? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to transfer data from one workbook to another with VB
FName = thisworkbook.range("A1") set MasterBk = workbooks.open(FName) FName = thisworkbook.range("A2") set CopyBk = workbooks.open(FName) MasterBk.sheets("Sheet1").cells.copy _ destination:=Copybk.sheets("sheet1").cells CopyBK.close savechanges:=true MasterBK.close savechanges:=False "Adnan" wrote: Joe, What is you have path of the workbook to copy from in cell A1, and in cell A2 the path of the workbook to copy to, and assume both workbooks are formatted the same, its just one has data the other is blank. Thank you, Adnan "Joel" wrote: FName = "c:\temp\book1.xls" set master = workbooks.open(FName) with master.sheets("Sheet1") LastRow = .Range("A" & rows.Count).end(xlup).Row NewRow = LastRow + 1 end with with Thisworkbook.Sheets("Sheet1") Tax = .Range("A20") with master.sheets("Sheet1") .Range("A" & NewRow) = Tax end with end with master.close savechanges:=true "Dan Worley" wrote: here is the scenario; I have a blank master invoice file which is opened and filled filled out when a customer makes a purchase. things like customer name information, product information, prices and totals. I have an existing command on the worksheet button that prints and saves the file. what i want to do is add to that command button VB code which will gather customer information, tax, subtotals and add that to a master ledger sheet in a seperate workbook at the next availible row starting in column A. i have some glimmerings on where to start but not enough to get going. anyone got an idea how to make this work? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to transfer data from one workbook to another with VB
Joel,
That answered my question. However, I was hoping I'd be able to implement this with that: http://www.microsoft.com/office/comm...0-a742428306e0 but had no luck. Your expertise would be greatly appreciated, Adnan :) "Joel" wrote: FName = thisworkbook.range("A1") set MasterBk = workbooks.open(FName) FName = thisworkbook.range("A2") set CopyBk = workbooks.open(FName) MasterBk.sheets("Sheet1").cells.copy _ destination:=Copybk.sheets("sheet1").cells CopyBK.close savechanges:=true MasterBK.close savechanges:=False "Adnan" wrote: Joe, What is you have path of the workbook to copy from in cell A1, and in cell A2 the path of the workbook to copy to, and assume both workbooks are formatted the same, its just one has data the other is blank. Thank you, Adnan "Joel" wrote: FName = "c:\temp\book1.xls" set master = workbooks.open(FName) with master.sheets("Sheet1") LastRow = .Range("A" & rows.Count).end(xlup).Row NewRow = LastRow + 1 end with with Thisworkbook.Sheets("Sheet1") Tax = .Range("A20") with master.sheets("Sheet1") .Range("A" & NewRow) = Tax end with end with master.close savechanges:=true "Dan Worley" wrote: here is the scenario; I have a blank master invoice file which is opened and filled filled out when a customer makes a purchase. things like customer name information, product information, prices and totals. I have an existing command on the worksheet button that prints and saves the file. what i want to do is add to that command button VB code which will gather customer information, tax, subtotals and add that to a master ledger sheet in a seperate workbook at the next availible row starting in column A. i have some glimmerings on where to start but not enough to get going. anyone got an idea how to make this work? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to transfer data from one workbook to another with VB
Something like this
FName = thisworkbook.range("A1") set MasterBk = workbooks.open(FName) FName = thisworkbook.range("A2") set CopyBk = workbooks.open(FName) for each sht in MasterBk.sheets sht.cells.copy _ destination:=Copybk.sheets(sht.name).cells next sht CopyBK.close savechanges:=true MasterBK.close savechanges:=False "Adnan" wrote: Joel, That answered my question. However, I was hoping I'd be able to implement this with that: http://www.microsoft.com/office/comm...0-a742428306e0 but had no luck. Your expertise would be greatly appreciated, Adnan :) "Joel" wrote: FName = thisworkbook.range("A1") set MasterBk = workbooks.open(FName) FName = thisworkbook.range("A2") set CopyBk = workbooks.open(FName) MasterBk.sheets("Sheet1").cells.copy _ destination:=Copybk.sheets("sheet1").cells CopyBK.close savechanges:=true MasterBK.close savechanges:=False "Adnan" wrote: Joe, What is you have path of the workbook to copy from in cell A1, and in cell A2 the path of the workbook to copy to, and assume both workbooks are formatted the same, its just one has data the other is blank. Thank you, Adnan "Joel" wrote: FName = "c:\temp\book1.xls" set master = workbooks.open(FName) with master.sheets("Sheet1") LastRow = .Range("A" & rows.Count).end(xlup).Row NewRow = LastRow + 1 end with with Thisworkbook.Sheets("Sheet1") Tax = .Range("A20") with master.sheets("Sheet1") .Range("A" & NewRow) = Tax end with end with master.close savechanges:=true "Dan Worley" wrote: here is the scenario; I have a blank master invoice file which is opened and filled filled out when a customer makes a purchase. things like customer name information, product information, prices and totals. I have an existing command on the worksheet button that prints and saves the file. what i want to do is add to that command button VB code which will gather customer information, tax, subtotals and add that to a master ledger sheet in a seperate workbook at the next availible row starting in column A. i have some glimmerings on where to start but not enough to get going. anyone got an idea how to make this work? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transfer data from a cell to another workbook if certain criteria | Excel Worksheet Functions | |||
How to transfer data from one workbook to another via VBA | Excel Discussion (Misc queries) | |||
Transfer data from one workbook to another workbook based on a key | Excel Programming | |||
Transfer data from one workbook to a form in another workbook | Excel Programming | |||
Data transfer from a template to a workbook | Excel Worksheet Functions |