Consulting

Results 1 to 4 of 4

Thread: VBA to pull values from cells off of second sheet

  1. #1

    VBA to pull values from cells off of second sheet

    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!

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,902
    Location
    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.

  3. #3
    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!

  4. #4
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •