We receive a spreadsheet from a vendor twice a month in the same format. It
includes 6 tabs in sheet. I need to pick out specific data from each
sheet....amount, account, etc. and put it in a separate sheet formated to
upload to our ERP system. Would a macro be the most effecient way to
accomplish this? Any suggestions? thanks.

Perhaps a looping macro something like

for each ws in worksheets
dlr=sheets("separatesheetname").cells(rows.count,1 ).end(xlup).row+1
if ws.name<"separatesheetname" then
with ws
sheets("separatesheetname").cells(dlr,"a")=.range( "a1").value
sheets("separatesheetname").cells(dlr,"b")=.range( "b1").value
next ws
end if
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pm" wrote in message
...
We receive a spreadsheet from a vendor twice a month in the same format.
It
includes 6 tabs in sheet. I need to pick out specific data from each
sheet....amount, account, etc. and put it in a separate sheet formated to
upload to our ERP system. Would a macro be the most effecient way to
accomplish this? Any suggestions? thanks.

Thanks Don....this gives me a direction...what about creating a new sheet to
store selected data?

"Don Guillett" wrote:

Perhaps a looping macro something like

for each ws in worksheets
dlr=sheets("separatesheetname").cells(rows.count,1 ).end(xlup).row+1
if ws.name<"separatesheetname" then
with ws
sheets("separatesheetname").cells(dlr,"a")=.range( "a1").value
sheets("separatesheetname").cells(dlr,"b")=.range( "b1").value
next ws
end if
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pm" wrote in message
...
We receive a spreadsheet from a vendor twice a month in the same format.
It
includes 6 tabs in sheet. I need to pick out specific data from each
sheet....amount, account, etc. and put it in a separate sheet formated to
upload to our ERP system. Would a macro be the most effecient way to
accomplish this? Any suggestions? thanks.

maye (untested)
sub makemasterandgetdata
sheets.add
for each ws in worksheets
dlr=cells(rows.count,1).end(xlup).row+1
if ws.name<activesheet.name then
with ws
cells(dlr,"a")=.range("a1").value
cells(dlr,"b")=.range("b1").value
next ws
end if
activesheet.name="mymastersheet"
end sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pm" wrote in message
...
Thanks Don....this gives me a direction...what about creating a new sheet
to
store selected data?

"Don Guillett" wrote:

Perhaps a looping macro something like

for each ws in worksheets
dlr=sheets("separatesheetname").cells(rows.count,1 ).end(xlup).row+1
if ws.name<"separatesheetname" then
with ws
sheets("separatesheetname").cells(dlr,"a")=.range( "a1").value
sheets("separatesheetname").cells(dlr,"b")=.range( "b1").value
next ws
end if
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pm" wrote in message
...
We receive a spreadsheet from a vendor twice a month in the same
format.
It
includes 6 tabs in sheet. I need to pick out specific data from each
sheet....amount, account, etc. and put it in a separate sheet formated
to
upload to our ERP system. Would a macro be the most effecient way to
accomplish this? Any suggestions? thanks.

Thanks for your assistance Don...I'll try it.

"Don Guillett" wrote:

maye (untested)
sub makemasterandgetdata
sheets.add
for each ws in worksheets
dlr=cells(rows.count,1).end(xlup).row+1
if ws.name<activesheet.name then
with ws
cells(dlr,"a")=.range("a1").value
cells(dlr,"b")=.range("b1").value
next ws
end if
activesheet.name="mymastersheet"
end sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pm" wrote in message
...
Thanks Don....this gives me a direction...what about creating a new sheet
to
store selected data?

"Don Guillett" wrote:

Perhaps a looping macro something like

for each ws in worksheets
dlr=sheets("separatesheetname").cells(rows.count,1 ).end(xlup).row+1
if ws.name<"separatesheetname" then
with ws
sheets("separatesheetname").cells(dlr,"a")=.range( "a1").value
sheets("separatesheetname").cells(dlr,"b")=.range( "b1").value
next ws
end if
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pm" wrote in message
...
We receive a spreadsheet from a vendor twice a month in the same
format.
It
includes 6 tabs in sheet. I need to pick out specific data from each
sheet....amount, account, etc. and put it in a separate sheet formated
to
upload to our ERP system. Would a macro be the most effecient way to
accomplish this? Any suggestions? thanks.