VBA to pull values from cells off of second sheet

[nirvehex]

Hi All,

I pieced together some code here from what I have gotten from another thread that I recently created and some of my own knowledge.



Sub UpdatePeakBottomCycles()
Dim lr2 As Long, lc2 As Long, lr3 As Long, ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Data Table") '<---- Change as required
Set ws2 = Sheets("Peak Bottom Cycles")
Sheets("Data Table").Select
lr2 = ws1.Cells(ws2.Rows.Count, 1).End(xlUp).Row
lc2 = ws1.UsedRange.Columns.Count
Sheets("Peak Bottom Cycles").Select
lr3 = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row
With ws2
ws2.Range("N23").Value = .Cells(lr3, 12)
ws2.Range("O32").Value = .Cells(lr2, 3)
ws2.Range("O33").Value = .Cells(lr2, 4)
ws2.Range("O34").Value = .Cells(lr2, 5)
ws2.Range("O35").Value = .Cells(lr2, 8)
End With
End Sub


Anyway, I must not be doing something correctly because the correct value in cell N23 populates, but none of the values in the O cells populate. I must not be defining my variable, lr2, correctly. Any ideas here? Thank you!

[Kenneth Hobs]

Do you see that your with ws2 uses the same worksheet object for both sides of the =?

While I don't see problem with your variable types, I would suggest using variable names that relate what they mean. While Long is ok for a column, Integer will suffice. For row numbers, always store as Long as you did.

[nirvehex]

Hi Kenneth. Thanks for the response. I'm still very much a beginner in VBA. I'm assuming you are talking about the With ws2 part of the code. I've revised the code the best I could and added comments to what I'm trying to do. Any help would be appreciated here.



Sub UpdatePeakBottomCycles()
Dim lr2 As Long, lr3 As Long, ws1 As Worksheet, ws2 As Worksheet 'declares variables
Set ws1 = Sheets("Data Table") 'sets ws1 as Data Table Sheet
Set ws2 = Sheets("Peak Bottom Cycles") 'sets ws2 as Peak Bottom Cycles Sheet
Sheets("Data Table").Select 'Goes to Data Table Sheet
lr2 = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row 'defines lr2 as last row in column 1 on data table sheet
Sheets("Peak Bottom Cycles").Select 'Goes to Peak Bottom Cycles Sheet
lr3 = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row 'defines lr3 as last row in column 1 on peak bottom cycles sheet
With ws2 'pastes last rows from select columns from Data table sheet to cells on the Peak Bottom Cycles sheet
ws2.Range("N23").Value = .Cells(lr3, 12)
ws2.Range("O32").Value = .Cells(lr2, 3)
ws2.Range("O33").Value = .Cells(lr2, 4)
ws2.Range("O34").Value = .Cells(lr2, 5)
ws2.Range("O35").Value = .Cells(lr2, 8)
End With
End Sub


To summarize: I'm trying to update N23 on the "Peak Bottom Cycles" sheet with the last cell in column 12 from this same sheet. Then I'm trying to update O32 with the last cell in column 3 from the "Data Table Sheet." I'm trying to do similar with O33, O34, and O35 as well, in pulling the last cells from column 4, 5, & 8 respectively also from the "Data Table Sheet". It would be so helpful if I could see some code that does this and compare it to mine above to see what I'm doing wrong. Thanks!

[nirvehex]

Wow! I just figured it out. I was so close! Modified code:



Sub UpdatePeakBottomCycles()
Dim lr2 As Long, lr3 As Long, ws1 As Worksheet, ws2 As Worksheet 'declares variables
Set ws1 = Sheets("Data Table") 'sets ws1 as Data Table Sheet
Set ws2 = Sheets("Peak Bottom Cycles") 'sets ws2 as Peak Bottom Cycles Sheet
Sheets("Data Table").Select 'Goes to Data Table Sheet
lr2 = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row 'defines lr2 as last row in column 1 on data table sheet
Sheets("Peak Bottom Cycles").Select 'Goes to Peak Bottom Cycles Sheet
lr3 = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row 'defines lr3 as last row in column 1 on peak bottom cycles sheet
With ws2 'pastes last rows from select columns from Data table sheet to cells on the Peak Bottom Cycles sheet
ws2.Range("N23").Value = .Cells(lr3, 12)
ws2.Range("O32").Value = ws1.Cells(lr2, 3)
ws2.Range("O33").Value = ws1.Cells(lr2, 4)
ws2.Range("O34").Value = ws1.Cells(lr2, 5)
ws2.Range("O35").Value = ws1.Cells(lr2, 8)
End With
End Sub


I was just missing the ws1. on the other side of the ws2.Range's. Kenneth thanks for pointing that out originally!