May 18 2022 12:59 PM
May 18 2022 02:42 PM
May 18 2022 04:49 PM
May 19 2022 06:21 AM
May 19 2022 07:00 AM
As I've understood your code, it should do the following:
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!
May 19 2022 07:14 AM