Forum Discussion
Move up to next blank row after copy/paste from previous sheet.
No, you should not be trying to create a separate process to move data up to blank cells on the next worksheet.
"What Ive noticed is only data in B2:B9 go to the exact same cell…"
That's because I wrote the code as you seemed to want it (you wrote "paste them in the same cells on the next sheet…", with no mention of avoiding non-blank cells in the destination), where the destination sheet is pulling data from the source sheet. Your described requirement was not clear.
But if the source sheet is (conditionally) pushing data to the next sheet, as I can see from this new post, but pushed from three separate source ranges to three separate destination ranges (also not mentioned in your earlier post). That requirement allows for simpler—though longer—code.
See the following.
Sub EndofDay()
'---- Get references to this worksheet and the next.
Dim wksSourceSheet As Worksheet
Dim wksNextSheet As Worksheet
'
Set wksSourceSheet = ActiveSheet
On Error Resume Next
Set wksNextSheet = ActiveWorkbook.Sheets(wksSourceSheet.Index + 1)
On Error GoTo 0
' -- Verify that there is a next worksheet.
If wksNextSheet Is Nothing Then
Call MsgBox("There is no next sheet in this workbook.")
Exit Sub
End If
'---- Define variables used in each of the next three code blocks.
Dim strRange As String
Dim rngSourceCell As Range
Dim rngColumnACell As Range
Dim strColumnAValue As String
Dim rngDestnCell As Range 'destination cell
'---- Conditionally copy the cells starting from the top of the IMPERATIVE
' range thru the end of that range to successive blank cells in the
' same range in column B on the next worksheet.
strRange = ("B2:B9")
Set rngDestnCell = wksNextSheet.Range("B2")
For Each rngSourceCell In wksSourceSheet.Range(strRange)
Set rngColumnACell = rngSourceCell.Offset(0, 1 - rngSourceCell.Column)
strColumnAValue = rngColumnACell.Value
If InStr(1, strColumnAValue, "T", vbTextCompare) > 0 Then
' Note: As long as there are no T's to the left of the IMPORTANT and
' FOLLOW UPS special cells, no further selection logic is needed.
' -- Find the next empty cell in the destination column.
Do Until rngDestnCell.Value = ""
Set rngDestnCell = rngDestnCell.Offset(1, 0)
Loop
' -- Copy the source cell to that cell.
rngDestnCell.Value = rngSourceCell.Value
'***You could add statements to copy other cell properties also.
End If
Next rngSourceCell
'---- Conditionally copy the cells starting from the top of the IMPORTANT
' range thru the end of that range to successive blank cells in the
' same range in column B on the next worksheet.
strRange = ("B11:B21")
Set rngDestnCell = wksNextSheet.Range("B11")
For Each rngSourceCell In wksSourceSheet.Range(strRange)
Set rngColumnACell = rngSourceCell.Offset(0, 1 - rngSourceCell.Column)
strColumnAValue = rngColumnACell.Value
If InStr(1, strColumnAValue, "T", vbTextCompare) > 0 Then
' Note: As long as there are no T's to the left of the IMPORTANT and
' FOLLOW UPS special cells, no further selection logic is needed.
' -- Find the next empty cell in the destination column.
Do Until rngDestnCell.Value = ""
Set rngDestnCell = rngDestnCell.Offset(1, 0)
Loop
' -- Copy the source cell to that cell.
rngDestnCell.Value = rngSourceCell.Value
'***You could add statements to copy other cell properties also.
End If
Next rngSourceCell
'---- Conditionally copy the cells starting from the top of the FOLLOW UPS
' range thru the end of that range to successive blank cells in the
' same range in column B on the next worksheet.
strRange = ("B23:B29")
Set rngDestnCell = wksNextSheet.Range("B23")
For Each rngSourceCell In wksSourceSheet.Range(strRange)
Set rngColumnACell = rngSourceCell.Offset(0, 1 - rngSourceCell.Column)
strColumnAValue = rngColumnACell.Value
If InStr(1, strColumnAValue, "T", vbTextCompare) > 0 Then
' Note: As long as there are no T's to the left of the IMPORTANT and
' FOLLOW UPS special cells, no further selection logic is needed.
' -- Find the next empty cell in the destination column.
Do Until rngDestnCell.Value = ""
Set rngDestnCell = rngDestnCell.Offset(1, 0)
Loop
' -- Copy the source cell to that cell.
rngDestnCell.Value = rngSourceCell.Value
'***You could add statements to copy other cell properties also.
End If
Next rngSourceCell
End SubAnd yes, during my working years I was very familiar with clients not giving me the full and correct definitions of what they needed/wanted on the first try.