0

I am cycling through all the Green Tabs in a workbook. When I come to a row of data where there is no value in Column G, I select that row, cut it, and open another workbook entitled "Unpaid AR." In that workbook, I find the first unused row, and Paste. Everything functions properly except for the Paste - nothing pastes, and I have tried several different techniques. Any ideas what could be going wrong?

Public Sub CutNPaste()
  Dim ws As Worksheet
  Dim ARRange As String
  Dim ARFilePath As String
  Dim ARcell As Range
  Dim CopyRange As String
  Dim i As Integer
  Dim varRange As String
    
  ARFilePath = "Unpaid AR.xlsx"

  For Each ws In ThisWorkbook.Worksheets
    ws.Activate
    If ws.Tab.ColorIndex = 10 Then  'If Tab is Green, Then...
      ReportRows = ActiveSheet.UsedRange.Rows.Count  'Get how many rows in the report
      Let ARRange = "G" & "2" & ":" & "G" & ReportRows 'Range to Inspect for Blanks
      i = 2
            
      For Each ARcell In Range(ARRange)
        Let CopyRange = "A" & i & ":" & "I" & i 'Set the copy range when blank is encountered
        If ARcell.Value = "" Then
          Range(CopyRange).Select
          Selection.Cut
          Workbooks.Open ARFilePath 'Open the Unpaid AR workbook
          Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select 'Find the first open row
          ActiveSheet.Paste 'This is where NOTHING happens
          Workbooks("Unpaid AR.xlsx").Close SaveChanges:=True 'Save and close destination wkbk
          Application.CutCopyMode = False
        End If
        i = i + 1
      Next ARcell
    End If
 Next ws
    
End Sub
Sudenga-Steve
  • 31
  • 5
  • Side notes: `Let` is deprecated, just drop that. Then [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Finally, the cut and paste steps should be "as close" to each other as possible, don't open a workbook inbetween, as that may clear the clipboard. – BigBen Feb 07 '22 at 14:55
  • Thank you BigBen. I removed 'Let' and also used 'Rows(i).Copy to Copy and 'ActiveCell.EntireRow.PasteSpecial' to Paste. Works great. – Sudenga-Steve Feb 07 '22 at 17:35

1 Answers1

0

i change a little bit...

Public Sub CutNPaste()
  Dim ws As Worksheet
  Dim ARRange As String
  Dim ARFilePath As String
  Dim ARcell As Range
  Dim CopyRange As Range
  Dim i As Integer
  Dim varRange As String
  Dim wkbTarget As Workbook
  Dim ReportRows As Long

  ARFilePath = ThisWorkbook.Path & "\Unpaid AR.xlsx"

  Set wkbTarget = Workbooks.Open(ARFilePath) 'Open the Unpaid AR workbook

  For Each ws In ThisWorkbook.Worksheets

    If ws.Tab.ColorIndex = 10 Then

      ReportRows = ws.UsedRange.Rows.Count
  
        
      For i = ReportRows To 2 Step -1
  
        Set CopyRange = ws.Range("A" & i & ":" & "I" & i)
    
        If ws.Cells(i, 7).Value = "" Then
      
          CopyRange.Cut Destination:=wkbTarget.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
      
          Application.CutCopyMode = False
        End If
    
      Next i
    End If
 Next ws

     wkbTarget.Close SaveChanges:=True 'Save and close destination wkbk

End Sub

BR Bernd

user18083442
  • 56
  • 2