change last cell in column

Originally Posted by jj33002

Well another question, thanks to mudraker I have this code

Sub Workbook_Open()
     
   Dim sWS As String
   Dim sMonths As Variant

   Select Case Day(Now())
   Case 1 To 5
      sMonths = Array("January", "February", "March", _
         "April", "May", "June", "July", "August", _
         "September", "October", "November", "December")
      sWS = sMonths(Month(Now()) - 1)
      Sheets(sWS).Range("b38").value = Sheets(sWS).Range("b38").value
   End Select
End Sub

It works great for what i want to do except the cell b38 will not always be b38 it will however be the last cell in column b. any ideas?

Hi

to find the last used cell in column B try

 iLastRow = ActiveSheet.Range("B65536").End(xlUp).Row

and to use that would be Range("B" & iLastRow). etc

hth
---

Sub Workbook_Open()
     
   Dim sWS As String
   Dim sMonths As Variant
   Dim iLastRow As Long

   iLastRow = ActiveSheet.Range("B65536").End(xlUp).Row

   Select Case Day(Now())
   Case 1 To 5
      sMonths = Array("January", "February", "March", _
         "April", "May", "June", "July", "August", _
         "September", "October", "November", "December")
      sWS = sMonths(Month(Now()) - 1)
      Sheets(sWS).Range("B"&iLastRow).value = Sheets(sWS).Range("B"&iLastRow).value
   End Select
End Sub

If you update your code to what's shown above, does that work?

UPDATE: Bryan pre-copied my reply.

Can you tell me what exactly you're trying to make this code do? Are you trying to make the value from C38 on one sheet into another sheet? Just looking at your worksheet isn't making it clearer to me (maybe it's too late and my brain is asleep.)

I think I found the problem..

In column B, row 54 you have the word "Total". It is making this the "iLastRow", and when the macro runs it's simply replacing "Total" with "Total". You need it to always find the last row in column B, so move "Total" to another column (C, for instance). Select all cells from B39 to B60 (or thereabouts) and click Delete just to be sure that range is empty.

Save your file and re-open it. The Workbook_Open macro should change the formula in B38 to the value, 272.96.

Let me know how that goes..