Inventory control

[mxt]

I have a spreadsheet with card numbers such as 12345678. that is used as a
master inventory list. Every week I have to compare my weeks worth of card
usage against the master list. I am currently copying and pasting each card
from one workbook to another. How can I compare the two workbooks to avoid
this?

 

[StumpedAgain]

Here's one option:

Open the workbooks you want to compare side by side.
On the Window menu, click Compare Side by Side with

Another option is to use the "compare and merge workbooks" found in the
Tools menu. In order to perform this operation, you have to have both
workbooks in the same file folder (with different names).

What you're looking for?

 

[mxt]

I am looking for equipment that has ben used throughout the week that is on
the master copy. I am looking for dplicate numbers on each workbook such as
2121222211 on one master and the same number on weekly

 

[StumpedAgain]

So you want to copy from the weekly sheet to the monthly sheet? If you have
an entry number of 2121222211 on the weekly sheet you want to find it on the
master sheet and copy information over? You could do this using vlookup
(then probably copy/paste values) or a macro. If you provide some additional
information (what you want to copy, where it's located, where you want to
copy it to, etc.), I can be more specific.

Let me know if this is the wrong direction.

 

[mxt]

I have a box with a number such as 12345678 on one master workbook from the
manufacturer. As the box gets used I put it on another workbook. At the end
of the week I need to find out where that box was used.. Right now I open
ctrl-f to open the dialog box to copy and paste the number. Then I look for
the card on the master copy. This takes along time because I have to copy
and paste 500 times and I have four regions to do.
I also have the two workbooks open now isde by side but I don't see the
compare command in the windows menu. Thank you for trying to help me

 

[StumpedAgain]

I'm not sure that this addressed all of your problem or is exactly what
you're looking for... so with that, the following macro puts the row in which
the number is found on the weekly sheet next to the number on the master
sheet. Note that you have to have both workbooks open in the same instance
and you will have to change the names (and possibly the start range) of your
workbook and sheets. Let me know if this doesn't do what you were after or
if you think we can tweak it so it can work.

If you're not familiar with macros, you do the following:

Copy the Code
Alt+F11 to start the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Select monthly (template) sheet
Alt+F8 to bring up Macros
Highlight the macro name
Run


Option Explicit
Sub FindMe()

Dim curselection, c As Range

Set curselection = Workbooks("Book2").Sheets("Sheet1").Range("A1") 'or
wherever you start

Do Until curselection = ""

With Workbooks("Book1").Sheets("Sheet1") 'weekly sheet
Set c = Sheets("Sheet1").UsedRange.Find(What:=curselection.Value)
If Not c Is Nothing Then curselection.Offset(0, 1) = c.Row
End With

Set curselection = curselection.Offset(1, 0)

Loop

End Sub

 

[StumpedAgain]

Slight modification to the code I just posted (it will look in the right spot
this time):

Option Explicit
Sub FindMe()

Dim curselection, c As Range

Set curselection = Workbooks("Book2").Sheets("Sheet1").Range("A1") 'or
wherever you start

Do Until curselection = ""

Set c =
Workbooks("Book1").Sheets("Sheet1").UsedRange.Find(What:=curselection.Value)
If Not c Is Nothing Then curselection.Offset(0, 1) = c.Row

Set curselection = curselection.Offset(1, 0)

Loop

End Sub