how to transfer data from one workbook to another with VB

  • Thread starter Thread starter Dan Worley
  • Start date Start date
D

Dan Worley

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?
 
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
 
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, it’s just one has data the other is blank.

Thank you,
Adnan
 
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
 
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
 
Back
Top