Code Needed for Project

[13tahall33]

Hello everyone,

I have decided to take my confusion to the forums, though I've never done this before, so I will do all that I can to make this as least painful as possible, and if I don't follow protocol exactly, I am deeply sorry- I am doing my best.

I am documenting sales velocity of over 400 individual pieces of art at my company, and need to scour workbooks with 52 sheets each (a calendar year of weeks) to find an individual line, its associated information, and record it in order to make a graph detailing its progress through the year.

For example, the art I need to find is "Art1", and I scroll the rows until I find it, and then in the cell directly to the left of it is the number of sales it made that week. I would then have to do this again 51 other times, over four locations where the art is active, more than 400 times as a result of the inventory needing mining. So you can imagine how having a button that more or less does all this for me instantly would be so incredible. I've done my best to composite code chunks into a functioning macro, but with my lack of knowledge in the subject, I'm grasping at straws and would love some assistance.

Here is the process I need the macro to do, as plainly and easily as I can make it--

1. Search sheet for art_name in column C (example variable)
2. Once located, look at cell directly to left in column B
3. Copy data in located cell
4. Paste data in a new sheet in the first available row cell of column G of sheet 52
5. Move to next sheet, and repeat 1 - 5

I feel like it sounds so simple, but I can't get it to work for the life of me.
Even recording the macro isn't quite working out for me because I need the code to repeat the process over again.

Any and all insight into getting this to work would be incredible,
Thank you,
~13tahall33

 

[DushiPunda]

1. Is it a guarantee that "Art1" (or whatever you're searching) will only appear in each sheet once or can it appear multiple times in the same sheet?
2. Will you open each of the workbooks manually and then run the macro, or do you intend to have the macro automatically open them?
3. If you want the macro to automatically open them, are they all in the same folder?

 

[13tahall33]

Good questions, thank you.
1. It is guaranteed that "Art1" will show up once per sheet, every time.
2. I intend to be able to move the macro around to various workbooks.
WHICH REMINDS ME
I'm so sorry, I realized for this to be any help, I'd need a user input window to start the initial search, so I can use the same macro for the entirety of art names in each workbook.

3. They are in the same folder, but its irrelevant as the macro would be independent of the files. I'd like to have it transferrable, even if it means I just quickly edit the object names in the code for a new document.

 

[DushiPunda]

Think i'm getting there. I wish I had read #1 from your reply correctly the first time because I just spent a ton of time trying to figure out a way to find subsequent entries of the same value without causing an infinite loop and crashing excel. As soon as I figured it out, I came here and reread your post haha. Anyway, so "art1" will only show up once per sheet, no matter what. Got it. I think I'll leave my code as is JUST IN CASE.

So, this is the general process you're planning on pursuing:
1. You have the macro in notepad or something of the such.
2. Open up one of the workbooks you want to look through.
3. Open VBA from that workbook
4. Copy and paste the macro from notepad
5. Run the macro
6. Collect your data???

Also, I'd like some clarification on this from your first post:

4. Paste data in a new sheet in the first available row cell of column G of sheet 52

So this should go into sheet 52 of the workbook you just opened? Is sheet 52 the last sheet in each workbook? Does sheet 52 have the same name in every single workbook? Originally I just thought you were after the total number (so find each entry from all the sheets and add it all together). But I should easily be able to make this work too.

 

[13tahall33]

You are correct, my friend.
I would like this macro to be a block of code I can transfer between workbooks.

For clarification, and some context-
Each workbook has 52 sheets, as it represents the 52 weeks in a year, so each sheet is titled: "Week-1", "Week-2", "Week-3", etc.
My apologies for not mentioning this sooner, I forgot they were named as such when I was writing my initial post.
Every workbook will have the same naming conventions. They are a catalog of around 250 rows, each with 5 columns of data.

An example would be:

| A B C D E |
|_____________________________________________|
| (00000000) | 45 | "Art1" | 1454 | 1104 |

So what I'm looking for is to user define the "Art1" tag, have the code look left to column B, copy the value of 45, and paste it in empty space on the last sheet of the workbook.
I don't need them added, I need them cataloged separately. I'm extracting the total, individual sales number from each week for a year in order to create a line graph with each week's sales number as the plots. I'm trying to find sales velocity of these pieces for data analysis.

So to answer your questions directly... Yes, Week-52 of the workbook I just opened. Yes, Week-52 is the last sheet in the workbook. Yes, Week-52 is the constant, static name given to the last page in every workbook.

Thank you for bearing with me.

 

[DushiPunda]

See how this works for you. Currently using "Sheets.Count" to identify the last worksheet...so if worksheet "Week-52" is the last worksheet (in terms of worksheet tab position), then it won't get put on the correct worksheet. But this should get you what you want.

Sub ArtLookup()
Dim sValue, rValueAddr As String, rValue As Range, lRow52 As Long, ws As Worksheet

On Error Resume Next

sValue = InputBox("Enter the value you want to search for:", "Search Value?")

If sValue = vbNullString Then Exit Sub

For Each ws In ThisWorkbook.Worksheets
    With ws.UsedRange
        Set rValue = .Cells.Find(What:=sValue, After:=.Range("A1"), _
                                    LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, MatchCase:=False)
                                    
        If Not rValue Is Nothing Then
            rValueAddr = rValue.Address
            
            Do
                Set rValue = .FindNext(rValue)
                If Worksheets(Sheets.Count).Range("G1").Value = "" Then
                    lRow52 = 0
                Else
                    lRow52 = Worksheets(Sheets.Count).Cells(Rows.Count, "G").End(xlUp).Row
                End If
                
                Worksheets(Sheets.Count).Range("G" & lRow52 + 1).Value = rValue.Offset(, -1).Value
            Loop While Not rValue Is Nothing And rValue.Address <> rValueAddr
        End If
    End With
    Set rValue = Nothing
Next
End Sub

Let me know if you have any issues and if you need or want anything changed/added!