Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Creating new sheet using VBA

Posted on 2016-11-14
2
Medium Priority
?
45 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:Judy Deo
[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 53

Accepted Solution

by:
Rgonzo1971 earned 2000 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:Judy Deo
ID: 41888408
thanks, it worked!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

670 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