Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I cannot edit cell format in Excel 2000 (Part of office 2000)! | Excel Discussion (Misc queries) | |||
Using Excel 2000 as Data source for Word 2000 document | Excel Discussion (Misc queries) | |||
Hyperlink in Excel 2000 can't open bookmarked Word 2000 file | Excel Discussion (Misc queries) | |||
Create macro to download access 2000 table to excel 2000 spreadsheet | Excel Programming | |||
Code-signing certificate problems in Excel 2000 with Windows 2000 | Excel Programming |