I have a spreadsheet that I need to take data from two columns and import
them into a seperate file. I will need to do this often, as the 1st
spreadsheet gets updated daily. And my final product is a monthly total. The
data I need is on a worksheet titled Totals(there's 3 sheets to the
workbook).
Here's my problem. The original data is not always located in the same cell
for example it could look like this one day:
Column B Column K
Row 24 Machine # Total
Row 25 0001 356
Row 26 0002 485
Row 27 0003 569

And the next day

Column B Column K
Row 24 Machine # Total
Row 25 0003 239
Row 26 0001 358
Row 27 0002 302

The machine #'s aren't committed to a certain row number, it's
auto-generated as a report, so I can't do anything about this, but the column
numbers will stay the same

My final product will be a monthly log sheet that will look like this:

Column A Column B
Row 01 Machine # Monthly Total
Row 02 0001 2356
Row 03 0002 3485
Row 04 0003 5569

So my macro needs to search down the row for the appropriate Machine
#(within a range of rows) and when it finds it return the value from Column K
into my Column B.

My VB is a little fuzzy, so excuse the mistakes but I think my format will
need to be somethign like
IF B25(Workbook, Worksheet) = A2 then B2 = K25(Workbook, worksheet) + B2
Else, Nextrow

Am I thinking along the right line?
Also, if you could tell me what my macro should really be, I'd appreciate it.
Also, will I need to do this update of my monthly report everyday? And will
the filename I'm drawing from need to be the same each time, or can I select
the file to draw from? (is that confusing?)

THANK-YOU
Any help is greatly appreciated, I'm having trouble finding all my answers
by looking thru help files.

Jenny,

Are you wanting to use worksheet functions to do this or VBA with a macro?
The formula you gave was in worksheet function format, vba would look more
like

sub totalmachines()
dim ran as range
dim wks as worksheet
dim iwks as worksheet
dim dailyTot as range
dim monthtot as range
set wks = activesheet
set iwks = workbooks("workbook to send to").sheets(1)
set ran = wks.columns("b").find(what:="machine#000", _
lookat:=xlwhole)
if ran is nothing then
msgbox "Machine # not found"
exit sub
end if
set dailytot = ran.offset(0,12)
'here we add it to the monthly total
set ran = iwks.columns("b").find(what:="machine#000", _
lookat:=xlwhole)
if ran is nothing then
msgbox "Machine # not found"
exit sub
end if
set monthtot = ran.offset(0,12)
monthtot = monthtot + dailytot
end sub




--
When you lose your mind, you free your life.


"Jenny" wrote:

I have a spreadsheet that I need to take data from two columns and import
them into a seperate file. I will need to do this often, as the 1st
spreadsheet gets updated daily. And my final product is a monthly total. The
data I need is on a worksheet titled Totals(there's 3 sheets to the
workbook).
Here's my problem. The original data is not always located in the same cell
for example it could look like this one day:
Column B Column K
Row 24 Machine # Total
Row 25 0001 356
Row 26 0002 485
Row 27 0003 569

And the next day

Column B Column K
Row 24 Machine # Total
Row 25 0003 239
Row 26 0001 358
Row 27 0002 302

The machine #'s aren't committed to a certain row number, it's
auto-generated as a report, so I can't do anything about this, but the column
numbers will stay the same

My final product will be a monthly log sheet that will look like this:

Column A Column B
Row 01 Machine # Monthly Total
Row 02 0001 2356
Row 03 0002 3485
Row 04 0003 5569

So my macro needs to search down the row for the appropriate Machine
#(within a range of rows) and when it finds it return the value from Column K
into my Column B.

My VB is a little fuzzy, so excuse the mistakes but I think my format will
need to be somethign like
IF B25(Workbook, Worksheet) = A2 then B2 = K25(Workbook, worksheet) + B2
Else, Nextrow

Am I thinking along the right line?
Also, if you could tell me what my macro should really be, I'd appreciate it.
Also, will I need to do this update of my monthly report everyday? And will
the filename I'm drawing from need to be the same each time, or can I select
the file to draw from? (is that confusing?)

THANK-YOU
Any help is greatly appreciated, I'm having trouble finding all my answers
by looking thru help files.

Thanks for your reply. I would like to use whichever is simpler, I'm doing
this for my father, so I want to make it as simple as possible. Thanks for
your help!!

"ben" wrote:

Jenny,

Are you wanting to use worksheet functions to do this or VBA with a macro?
The formula you gave was in worksheet function format, vba would look more
like

sub totalmachines()
dim ran as range
dim wks as worksheet
dim iwks as worksheet
dim dailyTot as range
dim monthtot as range
set wks = activesheet
set iwks = workbooks("workbook to send to").sheets(1)
set ran = wks.columns("b").find(what:="machine#000", _
lookat:=xlwhole)
if ran is nothing then
msgbox "Machine # not found"
exit sub
end if
set dailytot = ran.offset(0,12)
'here we add it to the monthly total
set ran = iwks.columns("b").find(what:="machine#000", _
lookat:=xlwhole)
if ran is nothing then
msgbox "Machine # not found"
exit sub
end if
set monthtot = ran.offset(0,12)
monthtot = monthtot + dailytot
end sub




--
When you lose your mind, you free your life.


"Jenny" wrote:

I have a spreadsheet that I need to take data from two columns and import
them into a seperate file. I will need to do this often, as the 1st
spreadsheet gets updated daily. And my final product is a monthly total. The
data I need is on a worksheet titled Totals(there's 3 sheets to the
workbook).
Here's my problem. The original data is not always located in the same cell
for example it could look like this one day:
Column B Column K
Row 24 Machine # Total
Row 25 0001 356
Row 26 0002 485
Row 27 0003 569

And the next day

Column B Column K
Row 24 Machine # Total
Row 25 0003 239
Row 26 0001 358
Row 27 0002 302

The machine #'s aren't committed to a certain row number, it's
auto-generated as a report, so I can't do anything about this, but the column
numbers will stay the same

My final product will be a monthly log sheet that will look like this:

Column A Column B
Row 01 Machine # Monthly Total
Row 02 0001 2356
Row 03 0002 3485
Row 04 0003 5569

So my macro needs to search down the row for the appropriate Machine
#(within a range of rows) and when it finds it return the value from Column K
into my Column B.

My VB is a little fuzzy, so excuse the mistakes but I think my format will
need to be somethign like
IF B25(Workbook, Worksheet) = A2 then B2 = K25(Workbook, worksheet) + B2
Else, Nextrow

Am I thinking along the right line?
Also, if you could tell me what my macro should really be, I'd appreciate it.
Also, will I need to do this update of my monthly report everyday? And will
the filename I'm drawing from need to be the same each time, or can I select
the file to draw from? (is that confusing?)

THANK-YOU
Any help is greatly appreciated, I'm having trouble finding all my answers
by looking thru help files.

well that's a manner of choice, with the worksheetfunction both must be
opened and saved manually, with the vba you have the choice of a lot more
automatics

--
When you lose your mind, you free your life.


"Jenny" wrote:

Thanks for your reply. I would like to use whichever is simpler, I'm doing
this for my father, so I want to make it as simple as possible. Thanks for
your help!!

"ben" wrote:

Jenny,

Are you wanting to use worksheet functions to do this or VBA with a macro?
The formula you gave was in worksheet function format, vba would look more
like

sub totalmachines()
dim ran as range
dim wks as worksheet
dim iwks as worksheet
dim dailyTot as range
dim monthtot as range
set wks = activesheet
set iwks = workbooks("workbook to send to").sheets(1)
set ran = wks.columns("b").find(what:="machine#000", _
lookat:=xlwhole)
if ran is nothing then
msgbox "Machine # not found"
exit sub
end if
set dailytot = ran.offset(0,12)
'here we add it to the monthly total
set ran = iwks.columns("b").find(what:="machine#000", _
lookat:=xlwhole)
if ran is nothing then
msgbox "Machine # not found"
exit sub
end if
set monthtot = ran.offset(0,12)
monthtot = monthtot + dailytot
end sub




--
When you lose your mind, you free your life.


"Jenny" wrote:

I have a spreadsheet that I need to take data from two columns and import
them into a seperate file. I will need to do this often, as the 1st
spreadsheet gets updated daily. And my final product is a monthly total. The
data I need is on a worksheet titled Totals(there's 3 sheets to the
workbook).
Here's my problem. The original data is not always located in the same cell
for example it could look like this one day:
Column B Column K
Row 24 Machine # Total
Row 25 0001 356
Row 26 0002 485
Row 27 0003 569

And the next day

Column B Column K
Row 24 Machine # Total
Row 25 0003 239
Row 26 0001 358
Row 27 0002 302

The machine #'s aren't committed to a certain row number, it's
auto-generated as a report, so I can't do anything about this, but the column
numbers will stay the same

My final product will be a monthly log sheet that will look like this:

Column A Column B
Row 01 Machine # Monthly Total
Row 02 0001 2356
Row 03 0002 3485
Row 04 0003 5569

So my macro needs to search down the row for the appropriate Machine
#(within a range of rows) and when it finds it return the value from Column K
into my Column B.

My VB is a little fuzzy, so excuse the mistakes but I think my format will
need to be somethign like
IF B25(Workbook, Worksheet) = A2 then B2 = K25(Workbook, worksheet) + B2
Else, Nextrow

Am I thinking along the right line?
Also, if you could tell me what my macro should really be, I'd appreciate it.
Also, will I need to do this update of my monthly report everyday? And will
the filename I'm drawing from need to be the same each time, or can I select
the file to draw from? (is that confusing?)

THANK-YOU
Any help is greatly appreciated, I'm having trouble finding all my answers
by looking thru help files.

Well which would work better if the source of the data's named changed daily.
Beacuse this report is generated automatically, so I'm assuming the name
might change according to the date.

"ben" wrote:

well that's a manner of choice, with the worksheetfunction both must be
opened and saved manually, with the vba you have the choice of a lot more
automatics

--
When you lose your mind, you free your life.


"Jenny" wrote:

Thanks for your reply. I would like to use whichever is simpler, I'm doing
this for my father, so I want to make it as simple as possible. Thanks for
your help!!

"ben" wrote:

Jenny,

Are you wanting to use worksheet functions to do this or VBA with a macro?
The formula you gave was in worksheet function format, vba would look more
like

sub totalmachines()
dim ran as range
dim wks as worksheet
dim iwks as worksheet
dim dailyTot as range
dim monthtot as range
set wks = activesheet
set iwks = workbooks("workbook to send to").sheets(1)
set ran = wks.columns("b").find(what:="machine#000", _
lookat:=xlwhole)
if ran is nothing then
msgbox "Machine # not found"
exit sub
end if
set dailytot = ran.offset(0,12)
'here we add it to the monthly total
set ran = iwks.columns("b").find(what:="machine#000", _
lookat:=xlwhole)
if ran is nothing then
msgbox "Machine # not found"
exit sub
end if
set monthtot = ran.offset(0,12)
monthtot = monthtot + dailytot
end sub




--
When you lose your mind, you free your life.


"Jenny" wrote:

I have a spreadsheet that I need to take data from two columns and import
them into a seperate file. I will need to do this often, as the 1st
spreadsheet gets updated daily. And my final product is a monthly total. The
data I need is on a worksheet titled Totals(there's 3 sheets to the
workbook).
Here's my problem. The original data is not always located in the same cell
for example it could look like this one day:
Column B Column K
Row 24 Machine # Total
Row 25 0001 356
Row 26 0002 485
Row 27 0003 569

And the next day

Column B Column K
Row 24 Machine # Total
Row 25 0003 239
Row 26 0001 358
Row 27 0002 302

The machine #'s aren't committed to a certain row number, it's
auto-generated as a report, so I can't do anything about this, but the column
numbers will stay the same

My final product will be a monthly log sheet that will look like this:

Column A Column B
Row 01 Machine # Monthly Total
Row 02 0001 2356
Row 03 0002 3485
Row 04 0003 5569

So my macro needs to search down the row for the appropriate Machine
#(within a range of rows) and when it finds it return the value from Column K
into my Column B.

My VB is a little fuzzy, so excuse the mistakes but I think my format will
need to be somethign like
IF B25(Workbook, Worksheet) = A2 then B2 = K25(Workbook, worksheet) + B2
Else, Nextrow

Am I thinking along the right line?
Also, if you could tell me what my macro should really be, I'd appreciate it.
Also, will I need to do this update of my monthly report everyday? And will
the filename I'm drawing from need to be the same each time, or can I select
the file to draw from? (is that confusing?)

THANK-YOU
Any help is greatly appreciated, I'm having trouble finding all my answers
by looking thru help files.

You could theoretically use worksheetfunction to do this, and would be easier
for the changing workbook names, but you have cell values changing themselves
which would run into problems this way, if you used a macro you could get
over this problem, but now you run into getting both the workbooks open at
the same time, I would reccomend, using a macro that is embedded in the
monthly .xls file, and then it asks which workbook to grab the data from.
Ben

--
When you lose your mind, you free your life.


"Jenny" wrote:

Well which would work better if the source of the data's named changed daily.
Beacuse this report is generated automatically, so I'm assuming the name
might change according to the date.

"ben" wrote:

well that's a manner of choice, with the worksheetfunction both must be
opened and saved manually, with the vba you have the choice of a lot more
automatics

--
When you lose your mind, you free your life.


"Jenny" wrote:

Thanks for your reply. I would like to use whichever is simpler, I'm doing
this for my father, so I want to make it as simple as possible. Thanks for
your help!!

"ben" wrote:

Jenny,

Are you wanting to use worksheet functions to do this or VBA with a macro?
The formula you gave was in worksheet function format, vba would look more
like

sub totalmachines()
dim ran as range
dim wks as worksheet
dim iwks as worksheet
dim dailyTot as range
dim monthtot as range
set wks = activesheet
set iwks = workbooks("workbook to send to").sheets(1)
set ran = wks.columns("b").find(what:="machine#000", _
lookat:=xlwhole)
if ran is nothing then
msgbox "Machine # not found"
exit sub
end if
set dailytot = ran.offset(0,12)
'here we add it to the monthly total
set ran = iwks.columns("b").find(what:="machine#000", _
lookat:=xlwhole)
if ran is nothing then
msgbox "Machine # not found"
exit sub
end if
set monthtot = ran.offset(0,12)
monthtot = monthtot + dailytot
end sub




--
When you lose your mind, you free your life.


"Jenny" wrote:

I have a spreadsheet that I need to take data from two columns and import
them into a seperate file. I will need to do this often, as the 1st
spreadsheet gets updated daily. And my final product is a monthly total. The
data I need is on a worksheet titled Totals(there's 3 sheets to the
workbook).
Here's my problem. The original data is not always located in the same cell
for example it could look like this one day:
Column B Column K
Row 24 Machine # Total
Row 25 0001 356
Row 26 0002 485
Row 27 0003 569

And the next day

Column B Column K
Row 24 Machine # Total
Row 25 0003 239
Row 26 0001 358
Row 27 0002 302

The machine #'s aren't committed to a certain row number, it's
auto-generated as a report, so I can't do anything about this, but the column
numbers will stay the same

My final product will be a monthly log sheet that will look like this:

Column A Column B
Row 01 Machine # Monthly Total
Row 02 0001 2356
Row 03 0002 3485
Row 04 0003 5569

So my macro needs to search down the row for the appropriate Machine
#(within a range of rows) and when it finds it return the value from Column K
into my Column B.

My VB is a little fuzzy, so excuse the mistakes but I think my format will
need to be somethign like
IF B25(Workbook, Worksheet) = A2 then B2 = K25(Workbook, worksheet) + B2
Else, Nextrow

Am I thinking along the right line?
Also, if you could tell me what my macro should really be, I'd appreciate it.
Also, will I need to do this update of my monthly report everyday? And will
the filename I'm drawing from need to be the same each time, or can I select
the file to draw from? (is that confusing?)

THANK-YOU
Any help is greatly appreciated, I'm having trouble finding all my answers
by looking thru help files.

That would be perfect. How do I set that up?

"ben" wrote:

You could theoretically use worksheetfunction to do this, and would be easier
for the changing workbook names, but you have cell values changing themselves
which would run into problems this way, if you used a macro you could get
over this problem, but now you run into getting both the workbooks open at
the same time, I would reccomend, using a macro that is embedded in the
monthly .xls file, and then it asks which workbook to grab the data from.
Ben

--
When you lose your mind, you free your life.


"Jenny" wrote:

Well which would work better if the source of the data's named changed daily.
Beacuse this report is generated automatically, so I'm assuming the name
might change according to the date.

"ben" wrote:

well that's a manner of choice, with the worksheetfunction both must be
opened and saved manually, with the vba you have the choice of a lot more
automatics

--
When you lose your mind, you free your life.


"Jenny" wrote:

Thanks for your reply. I would like to use whichever is simpler, I'm doing
this for my father, so I want to make it as simple as possible. Thanks for
your help!!

"ben" wrote:

Jenny,

Are you wanting to use worksheet functions to do this or VBA with a macro?
The formula you gave was in worksheet function format, vba would look more
like

sub totalmachines()
dim ran as range
dim wks as worksheet
dim iwks as worksheet
dim dailyTot as range
dim monthtot as range
set wks = activesheet
set iwks = workbooks("workbook to send to").sheets(1)
set ran = wks.columns("b").find(what:="machine#000", _
lookat:=xlwhole)
if ran is nothing then
msgbox "Machine # not found"
exit sub
end if
set dailytot = ran.offset(0,12)
'here we add it to the monthly total
set ran = iwks.columns("b").find(what:="machine#000", _
lookat:=xlwhole)
if ran is nothing then
msgbox "Machine # not found"
exit sub
end if
set monthtot = ran.offset(0,12)
monthtot = monthtot + dailytot
end sub




--
When you lose your mind, you free your life.


"Jenny" wrote:

I have a spreadsheet that I need to take data from two columns and import
them into a seperate file. I will need to do this often, as the 1st
spreadsheet gets updated daily. And my final product is a monthly total. The
data I need is on a worksheet titled Totals(there's 3 sheets to the
workbook).
Here's my problem. The original data is not always located in the same cell
for example it could look like this one day:
Column B Column K
Row 24 Machine # Total
Row 25 0001 356
Row 26 0002 485
Row 27 0003 569

And the next day

Column B Column K
Row 24 Machine # Total
Row 25 0003 239
Row 26 0001 358
Row 27 0002 302

The machine #'s aren't committed to a certain row number, it's
auto-generated as a report, so I can't do anything about this, but the column
numbers will stay the same

My final product will be a monthly log sheet that will look like this:

Column A Column B
Row 01 Machine # Monthly Total
Row 02 0001 2356
Row 03 0002 3485
Row 04 0003 5569

So my macro needs to search down the row for the appropriate Machine
#(within a range of rows) and when it finds it return the value from Column K
into my Column B.

My VB is a little fuzzy, so excuse the mistakes but I think my format will
need to be somethign like
IF B25(Workbook, Worksheet) = A2 then B2 = K25(Workbook, worksheet) + B2
Else, Nextrow

Am I thinking along the right line?
Also, if you could tell me what my macro should really be, I'd appreciate it.
Also, will I need to do this update of my monthly report everyday? And will
the filename I'm drawing from need to be the same each time, or can I select
the file to draw from? (is that confusing?)

THANK-YOU
Any help is greatly appreciated, I'm having trouble finding all my answers
by looking thru help files.