VBA copy between sheets stops after 5 entries.

[jamesj547]

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

 

[dave3009]

Presumably there's a blank cell in one of the ranges, thus triggering the Exit For?

 

[jamesj547]

It will write to the first 5 open rows in the range then the 6th it wont paste in the blank cell.

 

[dave3009]

So far as I can tell the Exit For is the only thing that would prematurely stop the code execution...so...let's try this, it might be easier.

Can you explain the procedure in words. What should happen during each step from start to finish. Something like...

Copy grid C3:H20 to the clipboard, loop over report C11:C32 and if it's blank paste the rows from C2:H20
Copy grid H23, loop over [sheet name] K2:K22 and if it's empty paste the value from H23
Clear the contents of grid C3:E22 and G3:G22

 

[jamesj547]

Sheet one table has data dynamically created by user selection from dropdown lists,
user activates macro that copies range, then it checks for a clear spot on sheet 2 table and pastes. User creates new data and and repeats. This will work fine until table two has 5 entries, then the paste just doesnt work..

 

[jamesj547]

 

[jamesj547]

 

[dave3009]

Try this on a copy of your workbook, NOT the original!!

Option Explicit

'************************************************************************
'*  Copies data from a user input grid to a report to send to client    *
'*  Author: dave3009                                                    *
'*  Date: 2022-09-26                                                    *
'************************************************************************

Sub ExportAndClear()
Dim lstReportRow As Long
Dim lstGridRow As Long

'Exports data from grid to report

With Sheets("Report")
    lstReportRow = .Range("C32").End(xlUp).Row                                              'Find the last row in the report sheet
    lstReportRow = IIf(lstReportRow < 11, 12, lstReportRow + 1)                             'Optional test that it if is below the headers

    .Range("C" & lstReportRow).Resize(21, 6).Value = Sheets("Grid").Range("C3:H20").Value   'Set the value of the report sheet to the grid of dropdowns
End With

'Reset the grid

With Sheets("Grid")

    lstGridRow = .Range("K22").End(xlUp).Row + 1                                            'Find the last row on the grid sheet column K
    .Range("K" & lstGridRow) = Sheets("Grid").Range("H23").Value                            'Set the value of that to the total of the report

    .Range("C3:E22,G3:G22").ClearContents                                                   'clear the grid
    .Range("C3").Select                                                                     'select the first cell

End With

Sheets("Grid").Activate                                                                      'ENsure the grid sheet is active

End Sub

 

[jamesj547]

You're a gentleman and a scholar. Thanks! that worked.

 

[jamesj547]

You're a gentleman and a scholar. Thanks! that worked.

Spoke too soon. its dumping erroneous data weird places.