Inventory Mngt with macros in excel2003

[dbeltran]

I am trying to create a spreadsheet for inventory management. Presently I have seventy separate worksheet. Each worksheet has data for one calendar day (ie. 08-10-07, then 08-11-07, and so on). Each worksheet has about 6000 rows with each row having inventory data for a different product we manufacture. Column A has the SKU number, and column D has the inventory on hand number.

What I need my macros to do is go through each worksheet, find the exact SKUs I'm looking for in column A, then pull the inventory value from the cell in column D in that row, and paste it into a cell in a new worksheet.

It would be nice if the macro could do this process repeatedly by going through all the spreadsheets. Some of my colleagues said that I may haev to create a loop and use soft variables. I really don't know enough about visual basic to add these in.

Anyway, I tried recording the basic function I want to be done repeatedly and this is what I came up with:

Windows("ORACLE_INV_06-01-07.xls").Activate
Cells.Find(What:="1009560", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("D548").Select
Selection.Copy
Windows("Book1").Activate
Range("C2").Select
ActiveSheet.Paste

I appreciate any suggestions you may have. If you need any more clarification, please let me know. Thanks.

 

[Datsmart]

You mentioned that you want to copy to a new worksheet, but in your code you are copying to a new workbook.
You also say you have "seventy separate worksheet". Would that mean 70 different workbooks too?
The answers to these questions will clarify what you are trying to do.

By the way, looks like you duplicated your post.
Only you or the forum moderator can delete the other post.
Click the X on the right side of your post, then answer yes to delete it.
http://www.mrexcel.com/board2/viewtopic.php?p=1387547&highlight=#1387547

EDIT: post deleted - Moderator

 

[Smitty]

Welcome to the Board!

You already appear to have a database (ORACLE), so why are you trying to recreate it in Excel (which you'll soon blow up with that much data anyway...)?

Wouldn't you be better off defining some queries in Oracle to get just the data you need?

Smitty

 

[dbeltran]

I did make a mistake with my wording. I have seventy different workbooks, and I am trying to condense everything into one new workbook with one spreadsheet only.