Range Selection not getting range I need-CodePudding

I am still very new to VBA. I have a workbook with multiple sheets that is updated monthly. On 2 of the sheets, I need to copy the last 5 columns that contain formulas, copy them (with number formatting) to the last blank column, then copy over the original range as values only. There is an empty column between Column A and Column I so in my code I am trying to start the range for xlToLeft to begin looking from column I. I am getting very confused trying to set up a range and nothing seems to be working. Here is the code that I've pieced together from other code I have. Please help.

Sub AgentReports()

Dim ws As Worksheet
Dim ArrayOne() As Variant
Dim wsName As Variant
Dim rngcopy As Range
Dim InTheList As Boolean


ArrayOne = Array("Sheet2", "Sheet3")

For Each ws In ThisWorkbook.Worksheets
        
        InTheList = Not (IsError(Application.Match(ws.CodeName, ArrayOne, 0)))
 
        
        If InTheList Then
        

        With ws
        
        Range("I3").Select
        
            Set rngcopy = .Range(.Cells(3, Columns.Count).End(xlToLeft).Offset(, -4), .Cells(Rows.Count, Columns.Count).End(xlToLeft))
            rngcopy.Copy rngcopy.Offset(, 5)
            rngcopy.Copy
            rngcopy.PasteSpecial Paste:=xlPasteValues
    
    End With

    
  End If
  
 Next ws

End Sub

CodePudding user response：

Try this:

Sub AgentReports()

    Dim ws As Worksheet, c As Range
    Dim ArrayOne() As Variant
    Dim wsName As Variant, lr As Long
    
    ArrayOne = Array("Sheet2", "Sheet3")
    For Each ws In ThisWorkbook.Worksheets
        If Not IsError(Application.Match(ws.CodeName, ArrayOne, 0)) Then
            
            'find the last used row
            lr = ws.Cells.Find(What:="*", After:=ws.Range("A1"), LookAt:=xlPart, _
                    LookIn:=xlFormulas, SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious).Row
            
            'find the last-occupied cell in Row 3
            Set c = ws.Cells(3, Columns.Count).End(xlToLeft)
            If c.Column >= 5 Then 'need 5 columns to copy
                With ws.Range(c.Offset(0, -4), ws.Cells(lr, c.Column))
                    .Select              'for testing only
                    .Copy .Offset(0, 5)  'copy 5 cols over
                    .Value = .Value      'convert to values
                End With
            End If
        End If
    Next ws

End Sub