I have a macro that adds the current date to the last non-zero cell in column A of a series of sheets titled "1" through "n". In column B of sheets "1" though "n", in the cell just to the right of where the date is entered, there is a vlookup function that looks for a sheet whose name is a date that matches the date entered in column A (in dd-mmm-yy format) and returns (in column B) a quantity from that sheet.
On some days, the quantity may be zero in column B and the next day will come and the macro will enter the new date in column A just below the previous date and all that will be in the row with the previous date is the date itself. If there are whole bunch of days in a row that this happens, this will take up too many valuable rows in my worksheet and my file size will get way too big.
Can anyone think of some code I could add that would clear the contents (not delete) of a row in sheets "1" though "n" on the condition that a new date is entered in column A and the previous day has a zero quantity in column B?
For example:
.............A.................B
1.........5-Jun-08.........""
2.........6-Jun-08.........35
If this were to happen, I want it to clear the contents of row 1 before the 6-Jun-08 is automatically entered into the worksheet so that 6-Jun-08 is entered into row 1 instead of row 2.
Here is my macro:
Private Sub Workbook_Open()
Dim xlWs As Worksheet
Dim fillRow As Long
Dim rng As Range
Dim i As Long
'First number in the following for loop is always 4, and the second number returns the total
'number of items in column B, which is found in the spreadsheet by using =max(B:B)
For i = 4 To Sheets("Initial Input").Range("O10").Value
'-- this tells which sheet to name what...
Set xlWs = Sheets(i)
'-- find the last non-empty cell in column A
fillRow = xlWs.Range("A65536").End(xlUp).Row
'-- Update sheet only if today's date has not already been entered into it
If xlWs.Range("A" & fillRow).Value <> Date Then
'-- enter today's date
xlWs.Range("A" & fillRow + 1).Value = Date
'-- fill down formula in column B
xlWs.Range("B" & fillRow).AutoFill Destination:=xlWs.Range("B" & fillRow & ":B" & fillRow + 1)
'-- fill down formula in column D
xlWs.Range("D" & fillRow).AutoFill Destination:=xlWs.Range("D" & fillRow & ":D" & fillRow + 1)
End If
Next i
Set xlWs = Nothing
Call CopyandDateSheet
End Sub
On some days, the quantity may be zero in column B and the next day will come and the macro will enter the new date in column A just below the previous date and all that will be in the row with the previous date is the date itself. If there are whole bunch of days in a row that this happens, this will take up too many valuable rows in my worksheet and my file size will get way too big.
Can anyone think of some code I could add that would clear the contents (not delete) of a row in sheets "1" though "n" on the condition that a new date is entered in column A and the previous day has a zero quantity in column B?
For example:
.............A.................B
1.........5-Jun-08.........""
2.........6-Jun-08.........35
If this were to happen, I want it to clear the contents of row 1 before the 6-Jun-08 is automatically entered into the worksheet so that 6-Jun-08 is entered into row 1 instead of row 2.
Here is my macro:
Private Sub Workbook_Open()
Dim xlWs As Worksheet
Dim fillRow As Long
Dim rng As Range
Dim i As Long
'First number in the following for loop is always 4, and the second number returns the total
'number of items in column B, which is found in the spreadsheet by using =max(B:B)
For i = 4 To Sheets("Initial Input").Range("O10").Value
'-- this tells which sheet to name what...
Set xlWs = Sheets(i)
'-- find the last non-empty cell in column A
fillRow = xlWs.Range("A65536").End(xlUp).Row
'-- Update sheet only if today's date has not already been entered into it
If xlWs.Range("A" & fillRow).Value <> Date Then
'-- enter today's date
xlWs.Range("A" & fillRow + 1).Value = Date
'-- fill down formula in column B
xlWs.Range("B" & fillRow).AutoFill Destination:=xlWs.Range("B" & fillRow & ":B" & fillRow + 1)
'-- fill down formula in column D
xlWs.Range("D" & fillRow).AutoFill Destination:=xlWs.Range("D" & fillRow & ":D" & fillRow + 1)
End If
Next i
Set xlWs = Nothing
Call CopyandDateSheet
End Sub