Pull Data, Manipulate, and Paste to New Sheet?

[dlongnecker]

As I attempt to familiarize myself with Excel’s VBA functionalities, I’ve been working on a “pseudo” spreadsheet before I apply it to a working case (out of one of our Excel trainer's books) in my organization.

I am attempting to create an automated sheet that will allow us to take information from a “Data Entry” sheet and build a report on the fly. I have three cells that contain formulas. The data is laid out:

Column A	Column B	Column C	Column D
Name		Sales #1	Sales #2	Sales #3

As it is, I’ve coded and can pull the name of the salesperson over to the “Report” sheet; however, I’m having difficulty parsing the rows’ data.

For example, in pseudo-code, I would envision:

For each row 5:END
	Sheet(3).Range(B) = ColumnB Value * Variable1
	Sheet(3).Range(C) = ColumnC Value * Variable2
	Etc.
Next

That would, in hope, spit out those numbers in the correct column (next to the person’s name) on the third sheet.

I’ve stumped our trainer~ Any suggestion or alternatives would be great. I’m sure I’m taking the “long road” in doing some of this. I’ve posted the existing code below and it has most of the necessary comments in it (so I don’t forget what I’m doing (^_^;).

Thanks!

-David

Sub GenerateSales()

' Ask user: are you sure you want to do this?

YesNo = MsgBox("This macro generate a current sales report. This will remove the previous report.  Do you want to continue?", vbYesNo + vbCritical, "Caution")
Select Case YesNo
Case vbYes
        
    ' Does a report already exist?
        
    Report = False
    For Each s In Worksheets
        If s.Name = "Report" Then
            Report = True
            Exit For
        End If
    Next s
    If Report = True Then
    
    ' Yeah, we've got a report.  Trash it.
    
        Application.DisplayAlerts = False
        Sheets("Report").Delete
        Application.DisplayAlerts = True
    End If
    
    ' Create a new report, set column widths, and name.
    
        Sheets.Add After:=Sheets(2)
        ActiveSheet.Name = "Report"
        Range("A1").ColumnWidth = 16
        Range("B1").ColumnWidth = 20

    ' Pull all salespersons names from Data Entry sheet to Report sheet.
    Sheets(2).Select
    Sheets(2).Range("B15").Select
    Call SelectColumn
        Selection.Copy
        Sheets(3).Select
        Range("B6").Select
        ActiveSheet.Paste
        Sheets(2).Select
        
    ' Begin manulipulating data from Data Entry sheet.
    
    ' Pull variables from Data Entry sheet, place into strings.
        
    Sheets(2).Select
    varStandard = Range("C6")
    varPriority = Range("C7")
    varNewBonus = Range("C9")
    
    
    
    ' Add the current date/time to the report.
    Sheets(3).Range("B2") = Now
    Sheets(3).Range("A2") = "Report Date:"
    Sheets(3).Range("A1") = "Pewter's Pottery"
    
    
    ' Completed; go to sheet Report.
    
    Sheets(3).Select

Case vbNo
End
End Select

End Sub

Sub SelectColumn()
Dim UpBound As Range
Dim LowBound As Range

If ActiveCell.Row > 1 Then
    If IsEmpty(ActiveCell.Offset(-1, 0)) Then
    Set UpBound = ActiveCell
    Else
    Set UpBound = ActiveCell.End(xlUp)
    End If
Else
    Set UpBound = ActiveCell
End If

If ActiveCell.Row < Rows.Count Then
    If IsEmpty(ActiveCell.Offset(1, 0)) Then
    Set LowBound = ActiveCell
    Else
    Set LowBound = ActiveCell.End(xlDown)
    End If
Else
    Set LowBound = ActiveCell
End If

Range(UpBound, LowBound).Select

Set UpBound = Nothing
Set LowBound = Nothing

End Sub

David R. Longnecker
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[VBAjedi]

David,

I'd like to see a sample of what you want the output to be - the finished report. Unless I'm missing what you're after, I think you could do this without any code at all.

Name the Data Entry sheet range A2:A100 "SourceNames", and A2100 "AllSourceData". Then create your Report sheet, set "A2:A100" to refer to "SourceNames", and use VLOOKUP formulas to pull the correct numbers from "AllSourceData".

Since you would be using formulas, the Report would always reflect what is on the Data Entry sheet, and you wouldn't need code at all.

VBAjedi

 

[dlongnecker]

Okay; let's see if I can ASCII draw this. (^_^)

Commissions	
	Standard	1.50%
	Priority	2.50%
		
New Customer Bonus	 $      50.00 
		

Salesperson		Std. Sales	Pri. Sales	New Cst.
				
Bob		41495	32275	0
Joe		50555	23006	1
				

Should become:

Salesperson	Standard	Priority	New Customers
Bob	622.43$	806.76$	0.00
Joe	758.33$	575.13$	50.00
			
			
Formulas are:

Standardsales * standardcomissionrate = standardcommission (on sheet 3)
Prioritysales * prioiritycommissionrate = prioritycommission (on sheet 3)
Newcustomers * newcustbonus = newcustomerbonus (on sheet 3)

There will also be some summaries and such in it; however, that part I've gotten figured out. As there will be lines after this, I was trying to avoid grabbing a huge section (hence: the subroutine for grabbing a specific section) and pasting it. Is there a way around that that's "pretty"? While this is "just for play", I'm trying to emulate some of the reporting requirements that will be needed for the real reports I hope to create, but, make it foolproof that I can turn over to a very novice Excel user (hence: the automation aspect).

-David

---

David R. Longnecker
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[dlongnecker]

VBAjedi-

After a frustrating evening trying to figure out my solution; I've been trying to figure out what you were talking about in yours... could you explain to me what you mean? I did a bit of research on VLOOKUPs; but, right now, I don't see how they'd accomplish what I'm looking for--the reading & manipulation of one set of data that is then output to another sheet automatically.

Could you please provide a bit more information?

Thanks!

-David

---

David R. Longnecker
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[VBAjedi]

What I was getting at is that reporting like this can often be done without any VBA coding whatsoever. Using Excel's built-in abilities is almost always faster, and generally less prone to errors. And then there's no need to "create" a new report every time your data changes - the report will update itself. Particularly powerful are the VLookup/HLookup functions, PivotTables, and the AutoFilter?Advanced Filter.

After looking at your desired end result, I think that a Pivot Table could do the trick for you here. Just set it up on a different sheet, and use formulas that perform the appropriate calculations for each Sales Amt field.

VBAjedi