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?

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?

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?

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?

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?

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?