VBA: Change Dim range to use text on active clipboard instead

nflami

New Member
Joined
Apr 16, 2014
Messages
25
Hello,

I need help slightly modifying some VBA code that I found in an old thread (Link to the old thread is at the bottom). This VBA is automatically changing the pivot filter based on the criteria in the predefined range.

What I'm trying to accomplish is to instead of using the predefined range, I would like to have it reference whatever I have copied to my active clipboard from another application, in my case it'll be only 1 numerical record.

Here is the original code.
Code:
Sub Step_Through_PageFilters()
    Dim c As Range
    Dim PT As PivotTable
    Set PT = Sheets("Item Lookup").PivotTables("PivotTable1")

    
    For Each c In Sheets("Random Demand").Range("O1:O24")
        With PT.PivotFields("ITEM_NUMBER")
            .ClearAllFilters
            On Error Resume Next
            .CurrentPage = c.Text
            On Error GoTo 0
            If .CurrentPage.Value = c.Text Then
        

                [COLOR=#008080]'Run other code here....[/COLOR]
              
                
            Else
                MsgBox c.Text & " pagefield item not found."
            End If
        End With
    Next c
End Sub

https://www.mrexcel.com/forum/excel...o-pivot-filter-visual-basic-applications.html

Thanks in advance!
Nic
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Nic

So you just want to get the value from the clipboard?

You can do that like this.
Code:
Dim objData As Object

    Set objData = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
  
    MyVal = objData.GetText
Not sure how that will fit in the existing code as that's working with a range of cells rather than a single value.
 
Upvote 0
Thanks! I was able to get the value off my clipboard with the above code. Now to figure out how to put the 2 parts together.

I may have to take the easy route and paste the value into a cell within my worksheet first, then use the range dimension in the original code, but hoping to keep the code cleaner to avoid formatting issues revolving around item #'s with leading zeros and adding extra steps.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top