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
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
Answered By – Tim Williams
Answer Checked By – David Goodson (AngularFixing Volunteer)