This code works until you get 5 records on the report table. I cant figure it out. Help please.
Sub ExportAndClear()
'EXPORT REPORT
Sheets("Grid").Select
Range("c3:h20").Copy
On Error Resume Next
Dim emptyReportCell As Range
For Each emptyReportCell In Worksheets("Report").Range("c11:c32").Cells
If Len(emptyReportCell) = 0 Then
emptyReportCell.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Exit For
End If
Next
' ExportAndClear Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Sheets("Grid").Select
Range("H23").Select
Selection.Copy
On Error Resume Next
Dim emptyCell As Range
For Each emptyCell In ActiveSheet.Range("k2:k22").Cells
If Len(emptyCell) = 0 Then
emptyCell.Select
Exit For
End If
Next
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Grid").Select
Range("C3:E22").Select
Selection.ClearContents
Range("G3:G22").Select
Selection.ClearContents
Range("C3").Select
Worksheets("Grid").Activate
'
End Sub
Sub ExportAndClear()
'EXPORT REPORT
Sheets("Grid").Select
Range("c3:h20").Copy
On Error Resume Next
Dim emptyReportCell As Range
For Each emptyReportCell In Worksheets("Report").Range("c11:c32").Cells
If Len(emptyReportCell) = 0 Then
emptyReportCell.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Exit For
End If
Next
' ExportAndClear Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Sheets("Grid").Select
Range("H23").Select
Selection.Copy
On Error Resume Next
Dim emptyCell As Range
For Each emptyCell In ActiveSheet.Range("k2:k22").Cells
If Len(emptyCell) = 0 Then
emptyCell.Select
Exit For
End If
Next
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Grid").Select
Range("C3:E22").Select
Selection.ClearContents
Range("G3:G22").Select
Selection.ClearContents
Range("C3").Select
Worksheets("Grid").Activate
'
End Sub