Solved

Creating new sheet using VBA

Posted on 2016-11-14
2
35 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 52

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

0. Preface This Article is a replacement of http:/A_1788-Getting-your-EE-Ranking-statistics-in-Excel.html (http://http:/A_1788-Getting-your-EE-Ranking-statistics-in-Excel.html). Changes in the way Experts Exchange delivers point statistics, impleme…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

632 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