@FJMSalgueiro
As I've understood your code, it should do the following:
- Remove all data from the Temp sheet
- Copy the data starting in cell A1 of the Import data sheet
- Paste that data as values on the Temp sheet
- Remove duplicates from the pasted data
- Autofit the columns of the pasted data
- Delete the last row of the pasted data
- Save the workbook
If I've understood correctly, then please try this:
Option Explicit
Public Sub Clean()
Dim wb As Workbook
Dim wsTemp As Worksheet
Dim wsImportData As Worksheet
Dim rng As Range
Set wb = ThisWorkbook
Set wsTemp = wb.Worksheets("Temp")
Set wsImportData = wb.Worksheets("Import Data")
'clear the temp sheet, then paste as values
With wsTemp
.Cells.Clear
.Activate
'get the data to be copied
'must be done after .Cells.Clear as this deactivates the copy
wsImportData.Range("A1").CurrentRegion.Copy
.Range("A1").PasteSpecial xlPasteValues
Set rng = .Range("A1").CurrentRegion
End With
'remove dupes, autofit and delete last row
rng.RemoveDuplicates Header:=xlYes
rng.EntireColumn.AutoFit
rng.Rows(rng.Rows.Count).Delete
'save the workbook
wb.Save
End Sub
Please note that if your data on the "Import Data" sheet does not start in cell A1 and possible contains entirely blank rows or columns between non-blank rows or columns, then you should replace this line:
wsImportData.Range("A1").CurrentRegion.Copy
With something like this:
Dim lastRow as Long
lastRow = wsImportData.Range("A5001").End(xlUp).Row
wsImportData.Range("A1:P" & lastRow).Copy
However, if your data starting in A1 is contiguous to the bottom and right of the table, .CurrentRegion will be good enough.
Good luck!
