Building a macro with a wildcard

[vanmeterkj]

Every week I copy this table out of OneNote and paste it into five or six different excel books to get the totals from all of the pages in each book. How do I put this formula into a macro?

Labor =sum('*'!m48)
Equipment =sum('*'!m55)
Materials =sum('*'!m62)
Total =sum('*'!m65)
Hours =sum('*'!k74)



Labor, Equipment, Materials, Total, & Materials are text in column 1 and the formulas are text in column 2. The cells referenced stay the same each week.

With my limited knowledge of macros I was only able to record the process of pasting the table. It worked great until I attempted to use it later. The macro only pasted what was currently on the clipboard, so when I had other data on the clipboard, the whole thing was a colossal failure!

I have tried to learn about the syntax required to build the macro in VBA, but I really suck at it!

Any help at all would be greatly appreciated!!!

Thanks,
Karen

[wallyeye]

Interesting, I had never used the asterisk like this before. It looks like it sums all the sheets other than itself, when entered in the Excel interface, but it translates it to the sheet names when you enter it. You can simulate this with a bit of VBA, assuming you are putting the formula on the first worksheet:

    Dim strwks2         As String
    Dim strwksend       As String

    strwks2 = Worksheets(2).Name
    strwksend = Worksheets(ThisWorkbook.Worksheets.Count).Name

    Range("A2:A23").Formula = "=sum(" & strwks2 & ":" & strwksend & "!F2)"

I tried putting the asterisk in the formula, Excel didn't like that. This first finds the name of the second and last worksheets, then embeds them in the formula.

[vanmeterkj]

I thought it might help to attach a sample of one of the weekly books I make. This particular job only had four sheets, but some jobs have as many as 40 sheets per week. I insert a new worksheet at the end and paste my previously stated formula into cell B4 and yes it does translate it to the sheet names of the current book. This particular book is for job 12106, but I have many jobs, ie. 11034 and 11186.

I made a genuine effort to paste the code into a new module and "let 'er run!" Colossal failure x 2! My VBA skills leave A LOT to be desired!!!

Can you tell me how I messed up with using the code? Your help is GREATLY appreciated!!

[wallyeye]

So, tweaking it to add a Summary worksheet a the end of the workbook, and adding an apostrophe to enclose worksheet names with spaces:

Public Sub CreateSums()

    Dim wksSum          As Worksheet

    Dim strwksStart     As String
    Dim strwksEnd       As String

    strwksStart= Worksheets(2).Name
    strwksend = Worksheets(ThisWorkbook.Worksheets.Count).Name

    Set wksSum = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(strwksend))
    wksSum.Name = "Summary"
    wksSum.Range("B4:B8").Value = Application.Transpose(Array("Labor", "Equipment", "Materials", "Total", "Hours"))
    wksSum.Range("C4").Formula = "=Sum('" & strwksStart & ":" & strwksend & "'!M48)"
    wksSum.Range("C5").Formula = "=Sum('" & strwksStart & ":" & strwksend & "'!M55)"
    wksSum.Range("C6").Formula = "=Sum('" & strwksStart & ":" & strwksend & "'!M62)"
    wksSum.Range("C7").Formula = "=Sum('" & strwksStart & ":" & strwksend & "'!M65)"
    wksSum.Range("C8").Formula = "=Sum('" & strwksStart & ":" & strwksend & "'!K74)"

    Set wksSum = Nothing

End Sub

[vanmeterkj]

SQUEALS with delight! It's ALIVE!

The only problem I have now is that I have to go into VBA each time I want to use it and move the module from my personal.xlsb into the book I am working in, essentially duplicating the number of steps I previously used when copying and pasting wildcard formula from OneNote.

What did I do wrong?

[wallyeye]

Hmmm... I thought I had changed this to worksheets(1). Incorporating Leith's comments:

Public Sub CreateSums()

    Dim wksSum          As Worksheet

    Dim strwksStart     As String
    Dim strwksEnd       As String

    with ActiveWorkbook
      strwksStart= .Worksheets(1).Name
      strwksend = .Worksheets(.Worksheets.Count).Name
      Set wksSum = .Worksheets.Add(After:=.Worksheets(strwksend))
    end with

    With wkssum
      .Name = "Summary"
      .Range("B4:B8").Value = Application.Transpose(Array("Labor", "Equipment", "Materials", "Total", "Hours"))
      .Range("C4").Formula = "=Sum('" & strwksStart & ":" & strwksend & "'!M48)"
      .Range("C5").Formula = "=Sum('" & strwksStart & ":" & strwksend & "'!M55)"
      .Range("C6").Formula = "=Sum('" & strwksStart & ":" & strwksend & "'!M62)"
      .Range("C7").Formula = "=Sum('" & strwksStart & ":" & strwksend & "'!M65)"
      .Range("C8").Formula = "=Sum('" & strwksStart & ":" & strwksend & "'!K74)"
    end with
    Set wksSum = Nothing

End Sub

[Leith Ross]

Hello vanmeterkj ,

The problem lies with the using the qualifier ThisWorkbook. When this encountered in your Personal.xlsb, VBA interprets this to mean you are referencing the Personal.xlsb workbook. Changing the qualifier from ThisWorkbook to ActiveWorkbook should fix the problem. The change has been made to the code below.

Public Sub CreateSums()

    Dim wksSum          As Worksheet

    Dim strwksStart     As String
    Dim strwksEnd       As String

    With ActiveWorkbook
        strwksStart= .Worksheets(2).Name
        strwksend = .Worksheets(.Worksheets.Count).Name
        Set wksSum =.Worksheets.Add(After:=.Worksheets(strwksend))
    End With

    wksSum.Name = "Summary"
    wksSum.Range("B4:B8").Value = Application.Transpose(Array("Labor", "Equipment", "Materials", "Total", "Hours"))
    wksSum.Range("C4").Formula = "=Sum('" & strwksStart & ":" & strwksend & "'!M48)"
    wksSum.Range("C5").Formula = "=Sum('" & strwksStart & ":" & strwksend & "'!M55)"
    wksSum.Range("C6").Formula = "=Sum('" & strwksStart & ":" & strwksend & "'!M62)"
    wksSum.Range("C7").Formula = "=Sum('" & strwksStart & ":" & strwksend & "'!M65)"
    wksSum.Range("C8").Formula = "=Sum('" & strwksStart & ":" & strwksend & "'!K74)"

    Set wksSum = Nothing

End Sub

[vanmeterkj]

I suspected it was because of the reference to 'ThisWorksheet', but I didn't know what to do about it. Where can I learn the rules for writing code? How do you know what terminology to use and the order that it is used?

Thank you so much for all your help!!

[wallyeye]

It is all about the Object Model. All of the MS applications, and many others, have a data model that represent their application and documents. After that it is just a bit of VBA and logic to tie it together. OK, a lot of VBA and logic. There are quite a few sites around with tutorials, I've learned a lot just participating in forums like this one, both reading and posting solutions.