Solved

Creating new sheet using VBA

Posted on 2016-11-14
2
34 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 51

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

734 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