Excel 2000 VB
 

I have a spreadsheet with all 31 days of the month on the left in cells B9:B39.
Data is entered in these cells and summed. At the end of the month a button
is pressed which runs code to take the sum (in cell B39 which is the 31st day
of the month) and this is pasted as an opening balance in B8. Then the data
cells are cleared and the use enters current month and year and starts the
data entering process again. Other calculations are based on this data as
well. If the month has less than 31 days, cell B8 ends up blank. I need to
write some code that selects cells B9:B39 and effectively find the last one
with data in it then start the copy/paste procedure. This is the code I have
so far:(I copy the result to cell R4 as you cannot copy, then cut the
original cell, then paste)
Private Sub CommandButton1_Click()


Range("B39").Select
Selection.Copy

Range("R4").Select
ActiveSheet.Paste

Range("B8").Select
Selection.ClearContents

Range("B9:C39").Select
Application.CutCopyMode = False

Selection.ClearContents

Range("R4").Select
Selection.Copy
Range("B8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B9").Select
End Sub

Excel 2000 VB
 

Give this a whirl

Range("B39").end(xlUp).Select 'The only change
Selection.Copy

Range("R4").Select
ActiveSheet.Paste

Range("B8").Select
Selection.ClearContents

Range("B9:C39").Select
Application.CutCopyMode = False

Selection.ClearContents

Range("R4").Select
Selection.Copy
Range("B8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B9").Select
End Sub

or if you want some shorter code that does the same thing

Range("B39").end(xlUp).Copy Range("R4")
Range("B8").ClearContents
Range("B9:C39").ClearContents
Range("R4").Copy Range("B8")
Range("B9").Select
End Sub
--
HTH...

Jim Thomlinson


"Nigel Forge" wrote:

I have a spreadsheet with all 31 days of the month on the left in cells B9:B39.
Data is entered in these cells and summed. At the end of the month a button
is pressed which runs code to take the sum (in cell B39 which is the 31st day
of the month) and this is pasted as an opening balance in B8. Then the data
cells are cleared and the use enters current month and year and starts the
data entering process again. Other calculations are based on this data as
well. If the month has less than 31 days, cell B8 ends up blank. I need to
write some code that selects cells B9:B39 and effectively find the last one
with data in it then start the copy/paste procedure. This is the code I have
so far:(I copy the result to cell R4 as you cannot copy, then cut the
original cell, then paste)
Private Sub CommandButton1_Click()


Range("B39").Select
Selection.Copy

Range("R4").Select
ActiveSheet.Paste

Range("B8").Select
Selection.ClearContents

Range("B9:C39").Select
Application.CutCopyMode = False

Selection.ClearContents

Range("R4").Select
Selection.Copy
Range("B8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B9").Select
End Sub

Excel 2000 VB
 

Many thanks for your help Jim. Way easier than I thought!

"Jim Thomlinson" wrote:

Give this a whirl

Range("B39").end(xlUp).Select 'The only change
Selection.Copy

Range("R4").Select
ActiveSheet.Paste

Range("B8").Select
Selection.ClearContents

Range("B9:C39").Select
Application.CutCopyMode = False

Selection.ClearContents

Range("R4").Select
Selection.Copy
Range("B8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B9").Select
End Sub

or if you want some shorter code that does the same thing

Range("B39").end(xlUp).Copy Range("R4")
Range("B8").ClearContents
Range("B9:C39").ClearContents
Range("R4").Copy Range("B8")
Range("B9").Select
End Sub
--
HTH...

Jim Thomlinson


"Nigel Forge" wrote:

I have a spreadsheet with all 31 days of the month on the left in cells B9:B39.
Data is entered in these cells and summed. At the end of the month a button
is pressed which runs code to take the sum (in cell B39 which is the 31st day
of the month) and this is pasted as an opening balance in B8. Then the data
cells are cleared and the use enters current month and year and starts the
data entering process again. Other calculations are based on this data as
well. If the month has less than 31 days, cell B8 ends up blank. I need to
write some code that selects cells B9:B39 and effectively find the last one
with data in it then start the copy/paste procedure. This is the code I have
so far:(I copy the result to cell R4 as you cannot copy, then cut the
original cell, then paste)
Private Sub CommandButton1_Click()


Range("B39").Select
Selection.Copy

Range("R4").Select
ActiveSheet.Paste

Range("B8").Select
Selection.ClearContents

Range("B9:C39").Select
Application.CutCopyMode = False

Selection.ClearContents

Range("R4").Select
Selection.Copy
Range("B8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B9").Select
End Sub