Your file was huge at more than 62 megabytes. This was caused by the fact that for some reason, the used range in the first sheet went all the way down to row 1,048,576 and over to column CG. If you hold down the CTRL key and press the END key, the cursor will move to the last cell in the used range. If all the unused rows and columns are deleted, the file shrinks to 28
Kilobytes. That's quite a difference in file size. If all of your CGYSR sheets have the same problem, it will take the macro a very long time to run. If you run the macro below, it should delete all the extra rows and columns in all the sheets. Because of the large amount of cleaning it has to do, it may take a while to finish so be patient and let it finish. For the cleaning to be finalized, it is very important that as soon as the macro finishes running, you
immediately save the file without doing anything to it. Just go to FILE....SAVE. After saving it, close it and re-open it. Check the file size to make sure that the macro did in fact delete all the extra rows and columns.
VBA Code:
Sub DeleteExtraRowsandColumns()
Application.ScreenUpdating = False
Dim lastRow As Long, lCol As Long, ws As Worksheet
For Each ws In Sheets
With ws
.Activate
lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lCol = Cells(3, Columns.Count).End(xlToLeft).Column + 2
Range("A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
Rows(lastRow + 1 & ":" & ActiveCell.Row).EntireRow.Delete
Range(Cells(1, lCol + 1), Cells(1, ActiveCell.Column)).EntireColumn.Delete
End With
Next ws
Application.ScreenUpdating = True
End Sub
Next try this macro again and see if it runs properly. I made a minor change to reflect the fact that your data in Sheet1 starts in row 1.
VBA Code:
Sub ReplaceCell()
Application.ScreenUpdating = False
Dim v As Variant, i As Long, ws As Worksheet, fnd As Range
v = Sheets("Sheet1").Range("A1", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Value
For Each ws In Sheets
If ws.Name Like "CGYSR-*" Then
For i = 1 To UBound(v)
Set fnd = ws.UsedRange.Find(v(i, 1), LookIn:=xlValues, lookat:=xlWhole)
If Not fnd Is Nothing Then
MsgBox fnd.Address
fnd.Offset(4).Value = v(i, 3)
End If
Next i
End If
Next ws
Application.ScreenUpdating = True
End Sub
