Reference isn't valid

[SAM MADZLAN]

Hi, i'm in need for some assistance. my code is not working the macro is supposed to pivot the data but i don't know why the code doesn't run / can someone simplify the code.

Thanks in advance

Sub PREP()
'
' File preparation Macro
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False


Dim y As Long, x As Long, LastRow As Long, Target As Range
 
Set Target = Range("A7")




    Sheets(8).Select
    Application.ActiveSheet.name = VBA.Right(Target, 8)
    ActiveSheet.Select
    Range("A8").Select
    Range(Selection, Selection.End(xlDown).Offset(-3)).Select
    Selection.Copy
    Sheets.add after:=ActiveSheet
    Sheets(9).Select
    Application.ActiveSheet.name = VBA.Right(Target, 8) & "_DATA"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(7, 1), Array(22, 1), Array(67, 1), Array(83, 1), _
        Array(84, 1), Array(85, 1), Array(104, 1)), TrailingMinusNumbers:=True
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "PROD"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "SEG"
    
    LastRow = Cells(Rows.count, "A").End(xlUp).Row
    y = Range(Selection, Selection.End(xlDown)).Rows.count
    
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],3)"
    Selection.AutoFill Destination:=Range("E2:E" & LastRow & "")
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'PRODUCT CODE'!C[-6]:C[-5],2,0)"
    Selection.AutoFill Destination:=Range("F2:F" & LastRow & "")


    mymymy = VBA.Right(Target, 8) & "_DATA"
    Range("A1").Select
     Selection.CurrentRegion.Select
     DataArea = "mymymy!R1C1:R" & Selection.Rows.count & "C" & Selection.Columns.count
    Sheets.add after:=ActiveSheet
    Sheets(10).Select
    Application.ActiveSheet.name = VBA.Right(Target, 8) & "_PIVOT"
    'Sheets(10).name = "PIVOT"
    'Sheets("PIVOT").Select
    Cells(1, 1).Select
    
    sibat = VBA.Right(Target, 8) & "_PIVOT"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "DataArea", Version:=4).CreatePivotTable TableDestination:= _
    "", TableName:="PivotTable4", DefaultVersion:=4
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("SEG")
        .Orientation = xlRowField
        .Position = 1
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("PROD")
        .Orientation = xlRowField
        .Position = 2
    End With
    
     ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("DEBIT AMOUNT (RM)"), "Sum of DEBIT AMOUNT (RM)", xlSum
    End With
        
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Sum of DEBIT AMOUNT (RM)") _
                .NumberFormat = "#,##0.00"
                
   ActiveSheet.PivotTables("PivotTable4").PivotFields("Sum of DEBIT AMOUNT (RM)") _
                .caption = "(RM)"
    
End Sub

 

[baitmaster]

Welcome to the board

Your problem stems from a very common error made in VBA coding, in that you aren't clear on the best way to refer to the objects you want to work with

Pretty much everything you do will involve an OBJECT and a METHOD or PROPERTY. You'll see this when you record a macro, your code is constructed object.method and object.property. Part of the problem is that VBA records everything you do and much of it is unnecessary. So in your case you've recorded:

    Sheets(8).Select
    Application.ActiveSheet.Name = VBA.Right(Target, 8)

where:
sheets(8) = object
.select = method

application.activesheet = object
.name = property

But you could instead have written

Sheets(8).Name = VBA.Right(Target, 8)

sheet(8) = object
.name = property

In fact, one of the most important things you need to learn to do, is to remove pretty much EVERY .Select and corresponding Selection from your code, and on most occasions Activesheet too. Be specific about the objects you are working with, and always think object.method, object.property

The next point is about the accuracy of your "qualification", i.e. how each object is qualified or defined. There's a few things about your code that need to be considered

1) Set Target = Range("A7")
Because you haven't stated exactly which range("a7") you're referring to - and there's one on every worksheet - VBA has interpreted this as the one on whichever sheet is currently active. Since your code then changes the worksheet, chances are that this is the cause of your problem. A range on one worksheet can't be referred to as an object on another

2) Sheets(8).Select
You're referring to the eighth worksheet in Excel, but this will go wrong as soon as you reorder your worksheets. A similar problem is to refer to it by Excel name e.g. Sheets("Sheet1"), which fails if you rename it. Go and look in the VB Editor, in the Project Explorer window, you'll see each worksheet has 2 names. The name in brackets is the Excel name, but the other name is the VBA code name, which is really useful because you can refer directly to this object. So if the 8th sheet of your worksheet is called say "settings" in Excel, it might say [...Sheet8 (settings)...] in the VBE explorer window, and you can refer to it directly in VBA using the term Sheet8

To use these two points, your code when corrected would start to look like this:

With sheet8
    Set Target = .Range("A7")
    .Name = Right(Target, 8)
    Range(.Range("A8"), .Range("A8").End(xlDown).Offset(-3)).Copy
    
    Dim wsDest As Worksheet: Set wsDest = Sheets.Add
    wsDest.Name = Right(Target, 8) & "_DATA"
    wsDest.Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

................................

End With

Note, I've added a WITH too. Anything between WITH and END WITH, which starts with a .dot, automatically includes the object defined in the With statement, in this case sheet8

Hope this is clear

 

[SAM MADZLAN]

Thanks Baitmaster, i will clean up my code. This code is structured based on recorded macro. Thanks once again. Cheers