Posts by Guinaba

    Hi experts,


    I have the code below to refresh Powerquery and one pivot chart. It works however I have to run twice to be able to refresh both items. Any suggestion how to fix this issue?


    Code
    1. Sub RefreshQueries_Click()
    2. ActiveWorkbook.Connections("Query - qryAldiScanData").Refresh
    3. ' Application.Wait (Now + TimeValue("0:00:05"))
    4. Call RefreshData
    5. End Sub
    6. Sub RefreshData()
    7. Sheets("Graph").PivotTables("PivotTable1").PivotCache.Refresh
    8. ThisWorkbook.RefreshAll
    9. End Sub

    Hi guys,


    I am trying to find a way to organize the columns of my sheet in ascending order, these columns are a mix of week/year and month/year and they are dynamic, changing every week. I could use the code below, but the problem is when the header changes the code stop working. Any suggestion?



    StockDesPO/LineQty2320202420203020203120202620202520202720201/08/20201/09/20201/10/2020



    Hi guys,



    I using this code to copy a snapshot of sheet(1), renaming considering today's date and pasting as the last sheet in the workbook. However not sure how to break the pivot table link with datasource, making it not refreshable. Any suggestion,please?


    I have two arrays:

    RawData Array: having the raw data

    Calc Array: having the transformed data

    In the Calc array the column DEM needs to be summed grouping the data by column MTHID. Any suggestions, please.


    Code:


    Sub AddCalc()


    Dim RawData() As Variant

    Dim Calc() As Variant

    Dim Dim1 As Long, Counter As Long


    Sheet2.Activate


    'Add data range into the RawData array

    RawData = Range("A2", Range("A2").End(xlDown).End(xlToRight))


    Dim1 = UBound(RawData, 1)

    Dim2 = UBound(RawData, 2)


    ReDim Calc(1 To Dim1, 1 To Dim2)


    For Counter = 1 To Dim1

    Calc(Counter, 1) = RawData(Counter, 3)

    Calc(Counter, 2) = RawData(Counter, 16)

    Calc(Counter, 3) = RawData(Counter, 17)

    Calc(Counter, 4) = RawData(Counter, 6)


    Next Counter


    Worksheets.Add

    Range("A2", Range("A2").Offset(Dim1, 3)).Value = Calc

    [A1:D1] = [{"SKUCODE","MONTHYEAR","MTHID","DEM"}]


    Erase RawData

    Erase Calc


    End Sub



    Table:


    SKUCODE

    MONTHYEAR

    MTHID

    DEM

    2CSH00596-5005

    1/01/2020

    MTH1

    0

    2CSH00596-5005

    1/01/2020

    MTH1

    0

    2CSH00596-5005

    1/01/2020

    MTH1

    2

    2CSH00596-5005

    1/01/2020

    MTH1

    1

    2CSH00596-5005

    1/01/2020

    MTH1

    1

    2CSH00596-5005

    1/01/2020

    MTH1

    1

    2CSH00596-5005

    1/01/2020

    MTH1

    2

    2CSH00596-5005

    1/01/2020

    MTH1

    1

    2CSH00596-5005

    1/02/2020

    MTH2

    2

    2CSH00596-5005

    1/02/2020

    MTH2

    1

    2CSH00596-5005

    1/02/2020

    MTH2

    1

    2CSH00596-5005

    1/03/2020

    MTH3

    1

    2CSH00596-5005

    1/03/2020

    MTH3

    1

    2CSH00596-5005

    1/03/2020

    MTH3

    1

    2CSH00596-5005

    1/03/2020

    MTH3

    1

    2CSH00596-5005

    1/04/2020

    MTH4

    1

    2CSH00596-5005

    1/04/2020

    MTH4

    1

    2CSH00596-5005

    1/05/2020

    MTH5

    1

    2CSH00596-5005

    1/05/2020

    MTH5

    1

    2CSH00596-5005

    1/06/2020

    MTH6

    1

    2CSH00596-5005

    1/07/2020

    MTH7

    1

    2CSH00596-5005

    1/07/2020

    MTH7

    1

    2CSH00596-5005

    1/07/2020

    MTH7

    1

    2CSH00596-5005

    1/07/2020

    MTH7

    1

    2CSH00596-5005

    1/08/2020

    MTH8

    1

    2CSH00596-5005

    1/08/2020

    MTH8

    1

    2CSH00596-5005

    1/09/2020

    MTH9

    1

    2CSH00596-5005

    1/09/2020

    MTH9

    1

    2CSH00596-5005

    1/09/2020

    MTH9

    1

    2CSH00596-5005

    1/10/2020

    MTH10

    1

    2CSH00596-5005

    1/10/2020

    MTH10

    1

    2CSH00596-5005

    1/11/2020

    MTH11

    1

    2CSH00596-5005

    1/11/2020

    MTH11

    1

    2CSH00596-5005

    1/12/2020

    MTH12

    1

    2CSH00596-5005

    1/12/2020

    MTH12

    1

    Hi experts,

    Not sure why I am getting different results when calculating standard deviation in Excel and DAX using the same data sample.

    In Excel using STDEV.S(C9:N9) the result is 2176



    Mth_1

    Mth_2

    Mth_3

    Mth_4

    Mth_5

    Mth_6

    Mth_7

    Mth_8

    Mth_9

    Mth_10

    Mth_11

    Mth_12

    StDev




    12118

    8059

    9180

    6352

    6863

    6797

    8548

    7657

    8845

    7063

    6352

    2916

    2176

    Using DAX function STDEVX.S the result is 342

    STD (12mth):=STDEVX.S(RawData,CALCULATE([Mth1]+[Mth2]+[Mth3]+[Mth4]+[Mth5]+[Mth6]+[Mth7]+[Mth8]+[Mth9]+[Mth10]+[Mth11]+[Mth12])) = 342


    Gilly

    Hi experts,


    Before adding values into column K, I need to delete the previous records. I have created a for loop to go through the col. K range to check whether the cells are empty if not clear the content, but not sure why is not working.




    Hi KjBox,


    Thanks for replying, actually I am using two option buttons to populate the ChartName parameter. Though not sure why the code work only for the first chart (Chart2wks)) and when I select the other chart (Chart3wks) I keep getting the error:

    Runtime Error 481; Invalid Picture



    Set
    CurrentChart = ThisWorkbook.Sheets("Charts").ChartObjects(ChartName).Chart


    2 wks coverage option button

    Private Sub Opt2WksCov_Click()

    Call ChangeChart("Chart2wks")

    End Sub


    3 wks coverage option button

    Private Sub Opt3WksCov_Click()

    Call ChangeChart("Chart3wks")

    End Sub



    Sub ChangeChart (ChartName As String)


    Dim CurrentChart As Chart

    Dim FName As String


    FName = ThisWorkbook.Path & "\temp.gif"


    Set CurrentChart = ThisWorkbook.Sheets("Charts").ChartObjects(ChartName).Chart


    CurrentChart.Export Filename:=FName, filtername:="GIF"


    FormCharts.ImgChart.Picture = LoadPicture(FName)



    End Sub

    Hi experts,


    I am trying to show a PivotChart in a userform using the code below:



    However, I am getting the below error msg:


    Run-time error - The item with the specified name wan't found


    I believe I am using the incorrect object (chart) but not sure how to add a pivot chart in the line code.


    Appreciate any help.






    Hi experts,


    Someone could help me to use inputbox variables to populate the data to be used in this function.



    Function Cover (Stock As Double, Sales As Range) As Double

    s = Stock
    c = 0

    For Each sale In Sales.Cells
    If s = 0 Then Exit For
    If s >= Val(sale.Value) Then
    c = c + 1
    s = s - Val(sale.Value)
    Else
    c = c + s / Val(sale.Value)
    s = 0
    Exit For
    End If
    Next

    If s > 0 Then c = 9999

    Cover = c

    End Function


    Cheers,


    Gilly

    Hi experts,


    The code below groups data by agent name and the total revenue generated by each agent. However I need to include a date column in this table and use this column in my group condition as well, so as result and should have the table 2:


    Could someone help me to include the second condition in my inverse loop.




    [TABLE="border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td]

    Table 1
    Before running the macro: [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 319"]

    [tr]


    [td]

    Name

    [/td]


    [td]

    Phone

    [/td]


    [td]

    Sales/Call

    [/td]


    [/tr]


    [tr]


    [td]

    Guy

    [/td]


    [td]

    1-565-498-6512

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    John

    [/td]


    [td]

    1-565-498-6513

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Mary

    [/td]


    [td]

    1-565-498-6514

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Ted

    [/td]


    [td]

    1-565-498-6515

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Guy

    [/td]


    [td]

    1-565-498-6512

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    John

    [/td]


    [td]

    1-565-498-6513

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Mary

    [/td]


    [td]

    1-565-498-6514

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Ted

    [/td]


    [td]

    1-565-498-6515

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Guy

    [/td]


    [td]

    1-565-498-6512

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    John

    [/td]


    [td]

    1-565-498-6513

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Mary

    [/td]


    [td]

    1-565-498-6514

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Ted

    [/td]


    [td]

    1-565-498-6515

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Guy

    [/td]


    [td]

    1-565-498-6512

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    John

    [/td]


    [td]

    1-565-498-6513

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Mary

    [/td]


    [td]

    1-565-498-6514

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Ted

    [/td]


    [td]

    1-565-498-6515

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [td]

    [TABLE="border: 1, cellpadding: 0, cellspacing: 0"]

    [tr]


    [td]

    Table 1
    After running the macro: [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 319"]

    [tr]


    [td]

    Name

    [/td]


    [td]

    Phone

    [/td]


    [td]

    Sales/Call

    [/td]


    [/tr]


    [tr]


    [td]

    Guy

    [/td]


    [td]

    1-565-498-6512

    [/td]


    [td]

    $4.00

    [/td]


    [/tr]


    [tr]


    [td]

    John

    [/td]


    [td]

    1-565-498-6513

    [/td]


    [td]

    $4.00

    [/td]


    [/tr]


    [tr]


    [td]

    Mary

    [/td]


    [td]

    1-565-498-6514

    [/td]


    [td]

    $4.00

    [/td]


    [/tr]


    [tr]


    [td]

    Ted

    [/td]


    [td]

    1-565-498-6515

    [/td]


    [td]

    $4.00

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [/tr]


    [tr]


    [td]

    Table 2
    Before running the macro: [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 397"]

    [tr]


    [td]

    Name

    [/td]


    [td]

    Date

    [/td]


    [td]

    Phone

    [/td]


    [td]

    Sales/Call

    [/td]


    [/tr]


    [tr]


    [td]

    Guy

    [/td]


    [td]

    7/05/2018

    [/td]


    [td]

    1-565-498-6512

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    John

    [/td]


    [td]

    7/05/2018

    [/td]


    [td]

    1-565-498-6513

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Mary

    [/td]


    [td]

    7/05/2018

    [/td]


    [td]

    1-565-498-6514

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Ted

    [/td]


    [td]

    7/05/2018

    [/td]


    [td]

    1-565-498-6515

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Guy

    [/td]


    [td]

    7/05/2018

    [/td]


    [td]

    1-565-498-6512

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    John

    [/td]


    [td]

    7/05/2018

    [/td]


    [td]

    1-565-498-6513

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Mary

    [/td]


    [td]

    7/05/2018

    [/td]


    [td]

    1-565-498-6514

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Ted

    [/td]


    [td]

    7/05/2018

    [/td]


    [td]

    1-565-498-6515

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Guy

    [/td]


    [td]

    8/05/2018

    [/td]


    [td]

    1-565-498-6512

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    John

    [/td]


    [td]

    8/05/2018

    [/td]


    [td]

    1-565-498-6513

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Mary

    [/td]


    [td]

    8/05/2018

    [/td]


    [td]

    1-565-498-6514

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Ted

    [/td]


    [td]

    8/05/2018

    [/td]


    [td]

    1-565-498-6515

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Guy

    [/td]


    [td]

    9/05/2018

    [/td]


    [td]

    1-565-498-6512

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    John

    [/td]


    [td]

    9/05/2018

    [/td]


    [td]

    1-565-498-6513

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Mary

    [/td]


    [td]

    9/05/2018

    [/td]


    [td]

    1-565-498-6514

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Ted

    [/td]


    [td]

    9/05/2018

    [/td]


    [td]

    1-565-498-6515

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [td]

    Table 2
    After running the macro: [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 345"]

    [tr]


    [td]

    Name

    [/td]


    [td]

    Date

    [/td]


    [td]

    Phone

    [/td]


    [td]

    Sales/Call

    [/td]


    [/tr]


    [tr]


    [td]

    Guy

    [/td]


    [td]

    7/05/2018

    [/td]


    [td]

    1-565-498-6512

    [/td]


    [td]

    $2.00

    [/td]


    [/tr]


    [tr]


    [td]

    John

    [/td]


    [td]

    7/05/2018

    [/td]


    [td]

    1-565-498-6513

    [/td]


    [td]

    $2.00

    [/td]


    [/tr]


    [tr]


    [td]

    Mary

    [/td]


    [td]

    7/05/2018

    [/td]


    [td]

    1-565-498-6514

    [/td]


    [td]

    $2.00

    [/td]


    [/tr]


    [tr]


    [td]

    Ted

    [/td]


    [td]

    7/05/2018

    [/td]


    [td]

    1-565-498-6515

    [/td]


    [td]

    $2.00

    [/td]


    [/tr]


    [tr]


    [td]

    Guy

    [/td]


    [td]

    8/05/2018

    [/td]


    [td]

    1-565-498-6512

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    John

    [/td]


    [td]

    8/05/2018

    [/td]


    [td]

    1-565-498-6513

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Mary

    [/td]


    [td]

    8/05/2018

    [/td]


    [td]

    1-565-498-6514

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Ted

    [/td]


    [td]

    8/05/2018

    [/td]


    [td]

    1-565-498-6515

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Guy

    [/td]


    [td]

    9/05/2018

    [/td]


    [td]

    1-565-498-6512

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    John

    [/td]


    [td]

    9/05/2018

    [/td]


    [td]

    1-565-498-6513

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Mary

    [/td]


    [td]

    9/05/2018

    [/td]


    [td]

    1-565-498-6514

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [tr]


    [td]

    Ted

    [/td]


    [td]

    9/05/2018

    [/td]


    [td]

    1-565-498-6515

    [/td]


    [td]

    $1.00

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [/tr]


    [/TABLE]
    Cheers,
    Gilly

    Hi experts,


    I am using the below code to copy and paste charts into a PowerPoint presentation, however I would like to paste in the correct (height and width). I am using PasteSpecial to paste but not sure how to position the pic.


    Sub ExceltoPowerPoint()

    Dim PowerPointApp As Object
    Dim myPresentation As Object

    Set PowerPointApp = GetPowerPointApp()
    Set myPresentation = PowerPointApp.Presentations.Add

    Call createPlanOverviewSlide(myPresentation, ThisWorkbook.ActiveSheet.Range("B3:N30"))

    PowerPointApp.Visible = True
    PowerPointApp.Activate

    Application.CutCopyMode = False




    Sub createPlanOverviewSlide(ByVal myPresentation As Object, ByRef rng As Range)

    'Create new slide------------------------------------------------------------------------------------------------------

    Set myslide = myPresentation.Slides.Add(myPresentation.Slides.Count + 1, 12) '11 = ppLayoutTitleOnly

    'Copy range and paste to powerpoint------------------------------------------------------------------------------------

    rng.Copy
    myslide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile



    End Sub

    Hi experts,


    I am using the following VBA to export my worksheet to PowerPoint file, however if I change the slicer in my Pivot Chart and export again the worksheet, the code is creating a new presentation. I would like to add the new selection using the same presentation, just adding as a new slide.


    Any suggestions, please.


    *******************************************************************************************************************************************************************************************************************************************************************************************************


    VBA Code:


    Sub ExceltoPowerPoint()


    Dim PowerPointApp As Object
    Dim myPresentation As Object
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim namecheck As Range


    Set PowerPointApp = GetPowerPointApp()
    Set myPresentation = PowerPointApp.Presentations.Add

    Call ExportResourcePlanSlide(myPresentation, ThisWorkbook.ActiveSheet.Range("a2:m40"))


    PowerPointApp.Visible = True
    PowerPointApp.Activate


    Application.CutCopyMode = False

    End Sub




    Function GetPowerPointApp() As Object


    On Error Resume Next
    Set PowerPointApp = GetObject(class:="PowerPoint.Application")
    Err.Clear
    If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(class:="PowerPoint.Application")
    If Err.Number = 429 Then
    MsgBox "PowerPoint could not be found, aborting."
    Exit Function
    End If
    Set GetPowerPointApp = PowerPointApp
    On Error GoTo 0

    End Function



    Sub ExportResourcePlanSlide(ByVal myPresentation As Object, ByRef rng As Range)


    'Create new slide------------------------------------------------------------------------------------------------------


    Set myslide = myPresentation.Slides.Add(myPresentation.Slides.Count + 1, 12) '11 = ppLayoutTitleOnly


    'Copy range and paste to powerpoint------------------------------------------------------------------------------------


    rng.Copy
    myslide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile


    'Add Commentary Text Box-----------------------------------------------------------------------------------------------


    Set myTextBox = myslide.Shapes.AddTextbox(1, Left:=100, Top:=100, Width:=8.19 * 28.3465, Height:=350)


    With myTextBox
    .TextFrame.TextRange.Text = ""
    .TextFrame.TextRange.Font.Size = 10
    .Left = 24.9 * 28.3465
    .Top = 3.18 * 28.3465
    End With


    End Sub

    Hello


    I've created an Excel 2016 User Form (UserForm1), and within that form, an image control (Image1). Using VBA, I can load images into the image control from a FILE, but I have not been able to load images from a Website (sharepoint)



    Here's the code that doesn't work... Any solutions?


    Cheers,
    Gilly