vba for vlookup to paste info onto next line down in next open cell in new workbook

[Saoirse]

Trying to copy with a vlookup info from the list from workbook "Shrinkage" sheet "Friday" then paste it onto workbook "Productivity tracker" sheet "Allie" on the next row down in columns Y:AD.

here afre the macros i keep trying but neither one seems to pull the info the paste onto the "Allie" spreadsheet on the next line down starting on column Y.
Sub shrinkpull()

Dim NextRow As Integer

NextRow = sheets("Allie").Cells(Rows.Count, 23).End(xlUp).Row + 1

sheets("Allie").Range("Y" & NextRow) = Workbooks("Shrinkage.xlsx").Worksheets("Monday").Range("C4:H4").copy _
("Productivity Tracker.xlsm").Worksheets("Allie").Range("Y & NextRow")

End Sub
Sub vlook()
'
Dim NextRow As Integer

NextRow = sheets("Allie").Cells(Rows.Count, 23).End(xlUp).Row + 1
'
Sheets("Allie").Range("Y" & NextRow)= WorksheetFunction.VLOOKUP([Shrinkage.xlsm](sheets("Friday").Range ("A4"),[Shrinkage.xlsm]sheets("Friday").Range("A3:H32"), 3,FALSE))


End Sub

Shrinkage.xlsm
	A		C	D	E	F	G	H
1	Enter the minutes worked for each category. EX: 1 hour = 60; 2 hours = 120		Compliance	System issues	Frontier	Letters / Special projects	Call pulls, fee calculations, large cases	buzz, trianing, coaching
2
3	Alexis
4	Allie		60	10	80	45	20	30
5	Aaron
6	Breonna
Friday

Productivity Tracker.xlsm
	A	B	C	D					I	J			M	N					S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
2	Date	Adjustments made	Prep Cases Worked	Time					Denial Closures	Time			ACE	Time					Approved Closure	Time	Worldpay Settlements	Time	Total Minutes Worked	% time working cases	Compliance	System issues	Frontier	Letters / Special Projects	Call pulls / fee calculations / large cases	buzz, trianing, coaching
3	2/1/2022	46	27	216					1	7				0					11	44	7	21	288	64%	60	10	80	45	20	30
4	2/2/2022	36	11	88					5	35			5	15					2	8	13	39	185	41%
5	2/3/2022	48	2	16					16	112			1	3					17	68	9	27	226	47%
6	2/6/2022	18	2	16					3	21				0					10	40	3	9	86	18%
Allie

Cell Formulas
Range	Formula
W3:W6	W3	=SUM(D3+J3+N3+T3+V3)
X3:X4	X3	=W3/450
Y3	Y3	=VLOOKUP('Z:\Departmental Shares\dOps\POC Training Material\Intake\Lesli Johnson\[Shrinkage.xlsm]Friday'!$A$4,'Z:\Departmental Shares\dOps\POC Training Material\Intake\Lesli Johnson\[Shrinkage.xlsm]Friday'!A$3:H$32,3,FALSE)
Z3	Z3	=VLOOKUP('Z:\Departmental Shares\dOps\POC Training Material\Intake\Lesli Johnson\[Shrinkage.xlsm]Friday'!$A$4,'Z:\Departmental Shares\dOps\POC Training Material\Intake\Lesli Johnson\[Shrinkage.xlsm]Friday'!A$2:H$32,4,FALSE)
AA3	AA3	=VLOOKUP('Z:\Departmental Shares\dOps\POC Training Material\Intake\Lesli Johnson\[Shrinkage.xlsm]Friday'!$A$4,'Z:\Departmental Shares\dOps\POC Training Material\Intake\Lesli Johnson\[Shrinkage.xlsm]Friday'!A$3:H$32,5,FALSE)
AB3	AB3	=VLOOKUP('Z:\Departmental Shares\dOps\POC Training Material\Intake\Lesli Johnson\[Shrinkage.xlsm]Friday'!$A$4,'Z:\Departmental Shares\dOps\POC Training Material\Intake\Lesli Johnson\[Shrinkage.xlsm]Friday'!A$3:H$32,6,FALSE)
AC3	AC3	=VLOOKUP('Z:\Departmental Shares\dOps\POC Training Material\Intake\Lesli Johnson\[Shrinkage.xlsm]Friday'!$A$4,'Z:\Departmental Shares\dOps\POC Training Material\Intake\Lesli Johnson\[Shrinkage.xlsm]Friday'!A$3:H$32,7,FALSE)
AD3	AD3	=VLOOKUP('Z:\Departmental Shares\dOps\POC Training Material\Intake\Lesli Johnson\[Shrinkage.xlsm]Friday'!$A$4,'Z:\Departmental Shares\dOps\POC Training Material\Intake\Lesli Johnson\[Shrinkage.xlsm]Friday'!A$3:H$32,8,FALSE)
X5:X6	X5	=W5/480
I3:I6,S3:S6	I3	=SUM(E3:H3)
M3:M6	M3	=SUM(K3:L3)

 

[Alex Blakenburg]

I haven't tested it but if you want the formula on the next row of Column Y I am not sure why you are using column 23 (W) to work out the last row.

Try changing this:

NextRow = sheets("Allie").Cells(Rows.Count, 23).End(xlUp).Row + 1

To this:

NextRow = sheets("Allie").Cells(Rows.Count, "Y").End(xlUp).Row + 1

 

[Saoirse]

Unfortunately that was unsuccessful. I am unable to make the vlookup formulas work within the macro at all. I have tried several different formulas and none seem to work to pull the information and paste to the next empty row of column Y. I am very very new to vba and am sure I look like a fool, but I really want to learn and figure this out.

I have tried all the below scenarios without results.

1)
Dim NextRow As Integer

NextRow = Sheets("Allie").Cells(Rows.Count, "Y").End(xlUp).Row + 1

Sheets("Allie").Range("Y" & NextRow) = Workbooks("Shrinkage.xlsx").Worksheets("Monday").Range("C4:H4").copy _
("Productivity Tracker.xlsm").Worksheets("Allie").Range("Y & NextRow")

End Sub

2)
Dim NextRow As Integer

NextRow = Sheets("Allie").Cells(Rows.Count, "Y").End(xlUp).Row + 1


VLookupResult = WorksheetFunction.VLookup(A4, shrinkage.Monday.A3:H35, 3, False)

3)
Dim NextRow As Integer

NextRow = Sheets("Allie").Cells(Rows.Count, "Y").End(xlUp).Row + 1


Sheets("Allie").Range("Y" & NextRow) = VLookupResult = WorksheetFunction.VLookup(A4, shrinkage.Monday.A3:H35, 3, False)


End Sub

4)
Dim NextRow As Integer

NextRow = Sheets("Allie").Cells(Rows.Count, "Y").End(xlUp).Row + 1


Sheets("Allie").Range("Y" & NextRow) = WorksheetFunction.Vlookup([Shrinkage.xlsm]Monday!$A$4,[Shrinkage.xlsm]Monday!A$3:H$33,3,FALSE)


End Sub

Nothing seems to get the info from one sheet onto the other

 

[Alex Blakenburg]

There was a lot more to that then the question indicated.
Ideally I would get answers to the following questions first but being in a different time zone I will post some code as well.

• I assume Productitivity Tracker contains a number of people, is this correct ?
  This would mean having to loop through the sheets
• How many days does the Workbook Shrinkage contain ?
  In the below I have assumed 1 week's worth with the days with sheets named Monday, Tuesday etc
  This means looping through the dates in the Productivity Tracker for each person.

I am assuming at this stage that the workbook Shrinkage is open and that the code is in the workbook Productivity Tracker.
You will need to list the worksheets to exclude (that are not sheets with the names of people to be tracked and joining them with AND in the if statement, replacing the ones I have)

Sub CopyShrinkage()

    ' Lookups are based on Individual's names in the sheetname
    ' Date using Day of Week as Shrinkage Sheet name
    
    Dim wbProd As Workbook, wbShrink As Workbook
    Dim shtProd As Worksheet, shtShkDay As Worksheet
    Dim prdFirstRow As Long, prdLastRow As Long
    Dim shkFirstRow As Long, shkLastRow As Long
    Dim dtCell As Range, dtCellDay As String
    Dim persName As String, shkPersRow As Variant
    Dim rngShkName As Range, rngShkPers As Range

    Set wbProd = ThisWorkbook
    Set wbShrink = Workbooks("Shrinkage.xlsm")
    
    prdFirstRow = 3
    shkFirstRow = 3
    
    For Each shtProd In wbProd.Worksheets
        ' Test for Names of sheets in Productivity Tracker that are not names of people and need to be excluded
        If shtProd.Name <> "Sample Monday" And _
            shtProd.Name <> "Allie Sample Output" Then
                prdLastRow = shtProd.Range("A" & Rows.Count).End(xlUp).Row
                ' Get Person name from sheet name
                persName = shtProd.Name
                            
                For Each dtCell In shtProd.Range("A" & prdFirstRow & ":A" & prdLastRow)
                    dtCellDay = Format(dtCell, "dddd")
                    Set shtShkDay = wbShrink.Worksheets(dtCellDay)
                    shkLastRow = shtShkDay.Range("A" & Rows.Count).End(xlUp).Row
                    Set rngShkName = shtShkDay.Range("A1" & ":A" & shkLastRow)
                    
                    If Not IsError(Application.Match(persName, rngShkName, 0)) Then
                        shkPersRow = Application.Match(persName, rngShkName, 0)
                        
                        Set rngShkPers = shtShkDay.Range("C" & shkPersRow & ":H" & shkPersRow)
                        shtProd.Range("Y" & dtCell.Row).Resize(, rngShkPers.Columns.Count).Value = rngShkPers.Value
                    Else
                        MsgBox persName & " Not found on " & dtCellDay
                    End If
                
                Next dtCell
        End If
    
    Next shtProd

End Sub

 

[Alex Blakenburg]

PS: I had issues with some of your names in shrinkage having spaces before/after the names, make sure that is cleaned up before running the code.

 

[Saoirse]

OMG WOW! this is waaaay more intense then I imagined!! I am so intimidated by this and didn't realize it would be this much. Sorry anyway, OK to answer your questions,

-The productivity tracker has about 30 tabs with each employee's name on it. Tab "allie" tab "Breonna", tab Courtney" etc I have another macro written to pull info from a tab and put it onto each employees tabs daily.
-The Shrinkage workbook has 5 tabs, "Monday", "Tuesday", "Wednesday", "Thursday" and "Friday" my analysts enter their information in the respective columns on the respective dates, daily. I planned on running the macro to pull the data from the shrinkage chart one day at a time and place it onto the respective analysts' tab in the productivity chart in columns Y through AD, 2-3 times per week to keep it updated.
-Yes, both workbooks are already opened and the code is in the Productivity Tracker.

 

[Alex Blakenburg]

The code I gave you will look at every date on the employee tab so it will redo previous days as it stands. Since previous days won't change it shouldn't matter that much.
It would be safer if your shrinkage workbook has all 5 days set up.

You might want to comment out the line MsgBox persName & " Not found on " & dtCellDay, I suspect you may get a lot of those and the message box will just get annoying.

You will need to change this line to exclude any non-employee sheets in the Productivity Tracker workbook.

       If shtProd.Name <> "Sample Monday" And _
            shtProd.Name <> "Allie Sample Output" Then

 

[Saoirse]

This error came up

Sub CopyShrinkage()

' Lookups are based on Individual's names in the sheetname
' Date using Day of Week as Shrinkage Sheet name

Dim wbProd As Workbook, wbShrink As Workbook
Dim shtProd As Worksheet, shtShkDay As Worksheet
Dim prdFirstRow As Long, prdLastRow As Long
Dim shkFirstRow As Long, shkLastRow As Long
Dim dtCell As Range, dtCellDay As String
Dim persName As String, shkPersRow As Variant
Dim rngShkName As Range, rngShkPers As Range

Set wbProd = ThisWorkbook
Set wbShrink = Workbooks("Shrinkage.xlsm")

prdFirstRow = 3
shkFirstRow = 3

For Each shtProd In wbProd.Worksheets
' Test for Names of sheets in Productivity Tracker that are not names of people and need to be excluded
If shtProd.Name <> "Adjustments" And _
shtProd.Name <> "Compilation" And _
shtProd.Name <> "timing" Then
prdLastRow = shtProd.Range("A" & Rows.Count).End(xlUp).Row
' Get Person name from sheet name
persName = shtProd.Name

For Each dtCell In shtProd.Range("A" & prdFirstRow & ":A" & prdLastRow)
dtCellDay = Format(dtCell, "dddd")
Set shtShkDay = wbShrink.Worksheets(dtCellDay) <- This line was highlighted
shkLastRow = shtShkDay.Range("A" & Rows.Count).End(xlUp).Row
Set rngShkName = shtShkDay.Range("A1" & ":A" & shkLastRow)

If Not IsError(Application.Match(persName, rngShkName, 0)) Then
shkPersRow = Application.Match(persName, rngShkName, 0)

Set rngShkPers = shtShkDay.Range("C" & shkPersRow & ":H" & shkPersRow)
shtProd.Range("Y" & dtCell.Row).Resize(, rngShkPers.Columns.Count).Value = rngShkPers.Value

End If

Next dtCell
End If

Next shtProd

End Sub

 

[Alex Blakenburg]

That error would indicate that you have a date in the Productivity Tracker Employee Sheet that you don't have a Day of the Week sheet for in your Shrinkage workbook.

For testing purposes, replace this line in the code (the error line)
Set shtShkDay = wbShrink.Worksheets(dtCellDay)

With this code
It should give you a message box telling you where the problem is in the Productivity Tracker workbook

                        On Error Resume Next
                    Set shtShkDay = wbShrink.Worksheets(dtCellDay)
                        If Err Then
                            MsgBox "Prod Sheet: " & vbTab & shtProd.Name & vbLf & _
                                "Cell: " & vbTab & vbTab & dtCell.Address & vbLf & _
                                "Cell Value: " & vbTab & dtCell & vbLf & _
                                "WeekDay Sheet: " & vbTab & dtCellDay
                            Exit Sub
                        End If
                        On Error GoTo 0

 

[Saoirse]

that would make sense. the productivity tracker only has the previous Date entered in with a daily macro i run. There wouldn't be a way to have the Shrinkage workbook match to a date. Which is why I just want it to fill in on the next row down in column Y... :/ I'm so sorry for all the confusion. I probably explained this all wrong in the beginning.

The shrinkage form is filled out daily by about 30 different people. Then I go into the productivity tracker and run a formula that takes Allie's info from the Shrinkage workbook for Monday and puts it onto Allie's worksheet in the productivity tracker on the next line down in column Y. But I would want it to work for every employee.
So I would want it to do the same for Alexis. Pull the info for Alexis from the Shrinkage workbook for Monday then paste it onto the next line down in column Y for the Alexis tab in the productivity tracker.

BUT then I need it to run for everyone for Tuesday, then for Wednesday etc. I got it to work individually with a vlookup or an index match, but i can't make the next macro run for the next line down in column Y on the productivity tracker. Is that even possible?