I am working on a macro to format some data of mine. It was working fine yesterday, but for some reason I'm getting a compile error today: "Message or data member not found". The problem seems to be focused on "Selection" and "ActiveSheet" towards the end of the procedure (in bold).
Any ideas?
Sub FormatData()
Dim ws As Worksheet
Set ws = ThisWorkbook.ActiveSheet
ws.Rows("1:16").Delete
ws.Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ws.Range("C1").Formula = "=DATEVALUE(Left(C2,8))"
ws.Range("C1").NumberFormat = "m/d/yyyy"
ws.Cells(1, 3).AutoFill Destination:=Range(Cells(1, 3), Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column))
ws.Columns("A:A").Select
Selection.Insert Shift:=xlRight, CopyOrigin:=xlFormatFromLeftOrAbove
ws.Range("A2").Value = "Brand"
ws.Range("A3").Formula = "=Concatenate(B3,""_"",C3,""_"",RIGHT($D$2,11))"
ws.Range("A3").AutoFill Destination:=Range("A3:A" & Cells(Rows.Count, 2).End(xlUp).Row)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ws.Range("D2").Select
ws.Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
ws.Selection.ClearContents
ws.Range("D1").Select
ws.Range(Selection, Selection.End(xlToRight)).Select
ws.Selection.cut
ws.Range("D2").Select
ws.ActiveSheet.Paste
End Sub
Bookmarks