Solved

Creating new sheet using VBA

Posted on 2016-11-14
2
31 Views
Last Modified: 2016-11-15
Using VBA, in the attached doc, how do I create a new sheet that contains the following:

Column 1 Header: Customer
Column 2 Header: Profit

Column 1 Value: <the text after "Customer : ">
Column 2 Value: In the line where it says "Customer Totals:", the value would be (L-G)/2

So an example the first rows of the sheet would like this:

Customer   Profit
13 n clark    1619.5
34ogden       556.5
Vincent inc   216
calc-test-1---part-2.xlsx
0
Comment
Question by:sagardeo
2 Comments
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41887394
Hi,

pls try
Sub macro()
Set shM = Sheets("Master")
res = Evaluate("=ISREF('Result'!A1)")
If res Then
    Sheets("Result").Activate
    Cells.ClearContents
Else
    Worksheets.Add after:=shM
    ActiveSheet.Name = "Result"
End If
Range("A1") = "Customer"
Range("B1") = "Profit"
For Each c In Range(shM.Range("A1"), shM.Range("A" & Rows.Count).End(xlUp))
    If c Like "Customer :*" Then
        Set Total = Nothing
        On Error Resume Next
        Set Total = shM.Columns(1).Find("Customer Total", LookAt:=xlPart, MatchCase:=False, LookIn:=xlValues, after:=c)
        If Not Total Is Nothing Then
            Range("A" & Rows.Count).End(xlUp).Offset(1) = Split(c, " : ")(1)
            Range("B" & Rows.Count).End(xlUp).Offset(1) = (Total.Offset(, 11) - Total.Offset(, 6)) / 2
            
        End If
    End If
Next
Columns("A:B").AutoFit
End Sub

Open in new window

Regards
0
 

Author Closing Comment

by:sagardeo
ID: 41888408
thanks, it worked!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Being an active EE Expert means to get a lot of (E)EMail, as you certainly know. If you are using Outlook, I'll show you how to minimize your inbox contents without losing anything – even improve the experience by changing the Subject line to facili…
Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question