Learning Excel Macros (2007) Handling Variables

[ladyck3]

From a previous thread ... I requested where to start to learn VBA and resoundingly the concensus is to create macros and read them... learn what the code is doing...

In that vein, I have pressed on. Here are my first findings.

========================================

I created 2 macros... the first one works, the second one doesnt but I know why it doesn't, its variables and I don't know if taming the variables is possible. If it is then just let me know. Its not important at this time that I tame the variable because I'm just trying to test the boundaries of a macro.

The first macro takes information I have copied from a report on the web and it Inserts a column between A and B so that I can use Text To Columns and separate the data at the comma.

02/15/2010,21:49:00

I need the dates only so I just split off the time. That works. I stopped recording because this next section did not work so I thought well record it separate and see what happens. I get it.

Sub PendPiv()
'
' PendPiv Macro
' ceate Pivot Table for Pending
'
' Keyboard Shortcut: Ctrl+t
'
    Columns("A:H").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R1048576C8", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet10!R3C1", TableName:="PivotTable2", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("Sheet10").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Date")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Date"), "Count of Date", xlCount
End Sub

Its breaking at the sheet reference because when it creates a new worksheet, the sheet number will change. So I deleted the one that I had,knowing the next numbered sheet would be Sheet10 and I entered that into the macro in the two sheet reference locations.

It created a pivot table but when I recorded the macro it did not catch one function.

The Date Column (Column A) when I create a pivot table manually is drug into the ROW LABELS box and it is also drug into the VALUES box because I am checking to see how many pending files we have and from what dates.

The macro did not drag the date into the ROW LABELS box, only into the VALUES box.

So the 2 things I'm wondering about are these:

1. How does one deal with the changing sheet number differences to use this daily. I do this EVERY DAY in a new workbook EVERY DAY.
2. Why didn't the ROW LABELS thing work but the VALUES does?

I am going to try this again but this time keep the pivot on the same page as the data and see if it works.

I tried the same creating the pivot table in column J on the sheet where the data resides but again the ROW Labels did not take, I was able to add them after the macro stopped however.

I'm never going to know what the sheet number may or may not be depending on how many interruptions I get during the day.

The first macro and a matching one to insert columns and split the text work FANTASTICALLY but the next question is this. How do I move this to the new workbook I create tomorrow?

How does one copy a macro from worksheet to worksheet?

(By the way... be careful what you suggest, you may very well be creating a MONSTER) hehehe


Thanks.... in advance

LadyCK3
aka: Laurie

 

[ladyck3]

OOPS I was thinking while I was typing....
I found how to drag and drop the module to another workbook via the Project Explorer window in the Visual Basic tool.

So I'm thinking out loud through my fingers now... so how do I create a toolbar to put these specific macros in or buttons for macros so I have it every time I open Excel? I wonder. That's part of that monster stuff

Just pondering is all

LadyCK3
aka: Laurie

 

[AnotherHiggins]

Are you in Excel 2007 or a previous version?

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ladyck3]

I'm using Excel 2007

LadyCK3
aka: Laurie

 

[SkipVought]

Regarding handy macros and UDFs, most of mine are in my PERSONAL.XLS workbook, that open automatically, invisible (with liberty and, er uh oops)

Question: Do you really intend to add PivotTables on the fly often? PT's are not the best place to begin, INHO. I hardly ever code PT's except to do grouping and refresh.

Your code, untested, on Excel 2003...

Sub PendPiv()
'
' PendPiv Macro
' ceate Pivot Table for Pending
'
' Keyboard Shortcut: Ctrl+t
'
    Dim wsNew As Worksheet
'where you are working with multiple sheet objects,
'each sheet object should be referenced

    Sheets("Sheet1").Columns("A:H").CurrentRegion.Name = "Database"
    
    Set wsNew = Sheets.Add
    
    With wsNew
        ActiveWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:="Database", _
            Version:=xlPivotTableVersion12).CreatePivotTable _
                TableDestination:=.Name & "!R3C1", _
                TableName:="PivotTable2", _
                DefaultVersion:=xlPivotTableVersion12
        
'        Sheets("Sheet10").Select
'        Cells(3, 1).Select

        With .PivotTables("PivotTable2").PivotFields("Date")
            .Orientation = xlRowField
            .Position = 1
        End With
        
        .PivotTables("PivotTable2").AddDataField
        
        .PivotTables("PivotTable2").PivotFields ("Date"), "Count of Date", xlCount
            
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

No I think Pivot Tables are easy enough to do, I did all of this counting and figuring manually for years and in the past 4 months learned pivot tables and my life is SO SO SO much simpler. Adding a button to create the pivot is silly.

I tried the macro you edited, and created a Sheet10 and what it did was create a fancy duplicate of the existing table with 'theme' coloring on that new worksheet.

I don't care to putz with this as an automation... the pivot table is a blessing enough in itself.. I was just testing the waters.

Personal.xlsm it is

LadyCK3
aka: Laurie

 

[SkipVought]

yes in the 2007 world, of which I have been avoiding as much as possible.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

oh Skip, don't avoid it, I was reluctant too but its MARVELOUS and Microsoft offers the most awesome-est tools for transitioning.

The help includes this or you can download it and use it at will from the PC.. its an interactive guide in which you use the menu commands from 2000/2003 and the screen morfs into the 2007 ribbon and shows you where the tool is or option.

Now getting around 2007 is something that *I* can help YOU with!

Here's the link to the page... I downloaded the modules for all of the apps to my PC and created shortcuts for quick access.

Seriously, check this out even if you don't have 2007 you can see how it works.

http://office.microsoft.com/en-us/training/HA102295841033.aspx

Skip try the interactive tool... seriously.

<---- the smile of me... giving back.

LadyCK3
aka: Laurie

 

[AnotherHiggins]

Here's a file I downloaded for the first custom add-in I ever made a few years ago. It's really easy to modify and maintain, and is a great tool to learn more VBA.

The following is from John Walkenbach's site (formerly J-Walk):

http://spreadsheetpage.com/index.php/file/menu_maker/

Click to download the example file named "menumakr.xls" (This is a very trustworthy site - the file is clean and safe to open)

For easy access to this (or any add-in) in Excel 2007, I have edited the QAT (Quick Access Toolbar) to display a new icon that, when clicked, displays my addin(s). Here's an example of what it looks like:

So when you click on the little list-looking icon at the top right of the picture above - which is always visible regardless of what Ribbon is active - it displays all of the custom menus you've created.

Here's how to do this in the QAT:
[ul][li]Go to Office Button > Excel Options > Customize[/li]

[li]In the Choose commands from: box, select All Commands[/li]

[li]Move it to the right-hand pane by pressing the Add button[/li]
[/ul]
(FYI: The pink eraser at the right of the QAT links directly to a custom macro)

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ladyck3]

JOHN.. I get that about the QAT however I have another real DEVIL in the works here at home.... I got a new laptop and it has Windows 7 (64bit) on it and that beast of an OS does not work like previous versions. I can't FIND the QAT file because the locations where it is 'supposed to' be are locked and I can't get access AND I am the admin on the stupid machine....

I'm told just now by a coworker to add the folder this is located in, share it to my homegroup which I don't have set up because I'm the only Win7 machine in the house and no one else is going to upgrade anytime soon....

SO maybe I can also tackle this... thanks SO much for the advise....

LadyCK3
aka: Laurie

 

[SkipVought]

Look for your [red]S[/red]

Otherwise you won't be able to FIND [red]S[/red]QAT! ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

It has nothing to do with a QAT File and is completely independent of the OS.

This is all done from within Excel 2007.

I've used Win7 64 bit at home since right after it's release and have no issues with this method.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ladyck3]

I opened up this menumaker file... and now my addins ribbon tab is GONE... and I've turned on all of the add-ins and its GONE... <sigh>

Later.....

LadyCK3
aka: Laurie

 

[ladyck3]

I got the addin tab back again but just a note.

I did not read thoroughly before using the file and just stupidly and blindly oepened it and started playing.... Then I lost the addins tab....but after I messed up with the menumaker file I went back to the site and that's when I saw this note:

Description: A table-driven technique that makes it very easy to create a custom menu for an Excel workbook or add-in. (Does not work with Excel 2007)

It does not work in 2007....

If you did get this to work that's AWESOME but me being not yet fluent, I think I screwed it all up

Yet another stupid user trick



LadyCK3
aka: Laurie

 

[AnotherHiggins]

Dang, I didn't notice that....

I must have adapted it when we switched to 2007. That was a fairly huge project for me since I had dozens of macros to test and rewrite. I don't remember having to tweak this code, but I guess I did. I'm sorry I lead you astray.

I'll take a look at one of my addins, compare it to what is included in the download, and post the edits I'm using, as I assure you it does work great in 2007.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ladyck3]

John...

At this point you don't need to go through that for 'me', anyway because I don't understand now to use it.



Baby Steps

LadyCK3
aka: Laurie