Macro to copy specific cell from multiple sheets to single sheet in different workbook

[keegan39]

Hi, I REALLY need some help, please! I can do basic macros and am expanding my skills, but not nearly to this level. Here's what I'm trying to do:

Every two weeks, two reports are run and are exported into Excel. Productivity workbook has a spreadsheet for each employee; PROD PPE workbook has one spreadsheet with each of the employees listed last name in column A and first name in column B.

I'm looking to create a macro that will take care of one of many tasks that needs to be done on these reports: Go into each sheet in Productivity (the employee's name is both the sheet tab name and also the data in cell B3) and copy cell C18 to the column H in PROD PPE for the corresponding employee. There is fairly regular turnover, so the employee names are not static.

I've created the macro to activate PROD PPE and concatenate the list of names so that I can search for them later in the process and then activate Productivity, but that is where I am stumped. I can figure out how to tell the macro to copy and paste - the basics. But I don't know how to search for the names or loop through the sheets, etc.

Here's the process as I see it. Please feel free to tell me if I'm off base: Concatenate the names in PROD PPE for searching; Activate Productivity; go to tab named Smith, John; move to cell C18, copy the data, activate PROD PPE; search for Smith, John; move to column H in that row; paste the data; activate Productivity; move to the next tab and begin the process again; continue until no more tabs; end process.

If anyone can help me, I will be VERY grateful. ANY part of this that you can help with will help to move me along.

Thank you!!

 

[Misca]

Welcome to the message board!

Try this:

Sub GetValuesFromProduct()

'Loops the name from PROD PPE and enters the values from matching sheets in PRODUCT


Dim Product As Workbook
Dim MyName As String
Dim c As Range
Dim Rng As Range


Application.ScreenUpdating = False
On Error Resume Next    'Macro won't crash if a sheet is missing


Set Product = Workbooks("Product.xlsx") 'Change to match your workbook name & have it open when running the macro


    With ActiveSheet    'Start the macro from the PROD PPE workbook!
        Set Rng = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))    'Rng starts from A2 and goes 'till the last non empty cell in that column
    
'Starts the loop:
        For Each c In Rng
            With c
                MyName = .Value & ", " & .Offset(, 1).Value
                .Offset(, 2).Value = Product.Sheets(MyName).Cells(18, 3).Value
            End With
        Next c
    End With


End Sub

The code is very simple and doesn't check if all the sheets from Product were used (= it ignores the sheets with no match in the names list). Also, if there's a name on the list with no match in the worksheets in Product it simply leaves that cell empty.

 

[keegan39]

Hi Misca,

Thanks so much for the help. I'm sorry for the delay in my response - with perfect timing, my family had a small emergency and that took my attention.

I really appreciate the help, and that's gotten me started. I can follow some of what you have here, so I'm REALLY grateful. but this isn't finding ANY matches, because all of the cells remain empty. If there's any further help that you're able to give, I'm grateful. If not, you've given me a jumping point that I can try to work from. Either way, you're wonderful Thank you!

 

[Misca]

Check the syntax of how the sheet names are created from columns A and B: The macro is expecting them to be column A value and column B value with ", " (=a comma and a space) between them. Make sure your columns don't have trailing spaces. Also, make sure the names are written the same way. VBA is very picky with upper and lower case letters. This shouldn't be the case here but it's always better to be safe than sorry.