0

I have the following code:

Dim StartCell3 As Range
Dim DataRange3 As Range


Set StartCell3 = Range("A1")
Set DataRange3 = StartCell3.CurrentRegion

DataRange3.Select

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataRange3, Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="Stocks!R1C6", TableName:="PivotTable8", DefaultVersion _
        :=xlPivotTableVersion15
    Sheets("Stocks").Select
    Cells(1, 6).Select
    With ActiveSheet.PivotTables("PivotTable8").PivotFields("Material")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable8").AddDataField ActiveSheet.PivotTables( _
        "PivotTable8").PivotFields("Unrestricted"), "Sum of Unrestricted", xlSum
    Range("F6").Select
    With ActiveSheet.PivotTables("PivotTable8")
        .ColumnGrand = False
        .RowGrand = False
    End With

When it comes to Sheets("Stocks").select, it gives subscript out of range error. It does not give any error while working on a module, but I created a macro enabled workbook and inserted a button that assigned with this macro.

The main problem is, from my created macro, the working code is on this workbooks code section as normally. Therefore, it only has sheet1, does not have sheet2 or some other sheet name. Why it gives an error? Why the workbook can not recognizes the other workbook?

hakandeep
  • 51
  • 1
  • 10

1 Answers1

0

Is the sheet "Stocks" in the workbook that you are running the macro from?

if not you will need to tell excel which workbook it is in.

For example:

Workbooks("name of workbook where the sheet is").Activate
Sheets("Stocks").Select

Although it is not recommended that you use activate or select as this can cause unexpected run time errors. Please see the following: How to avoid using Select in Excel VBA macros

Doog_Dooger
  • 127
  • 8
  • I already activated it before to do some operations, in the code I gave it works until Sheets("Stocks").select. Now it works when I run the macro from the module I created in the workbook I run the macro from. But when I send the excel to someone else, it also contains the module and the code but when try to run it from a button it gives error "cannot run macro......." – hakandeep Aug 23 '16 at 12:34
  • Check out this help topic to see if this resolves the issue as it sounds like they have not enabled macros by selecting enable content. [https://support.microsoft.com/en-us/kb/930076] – Doog_Dooger Aug 23 '16 at 15:50
  • I enabled, it was not the issue but thanks for your help – hakandeep Aug 24 '16 at 05:32