Solved

Automate Reporting in Excel

Posted on 2011-03-02
2
250 Views
Last Modified: 2012-05-11
Hello,

The below code is used to automate a 1 sheet report in excel. The code cycles through a list of names, and generates a unique report for each name.
I would like to add a second sheet, however, I'm not sure where to begin. This was written by someone who no longer works with me.

Thank you for your help.
jk

 
Dim objSheet As Worksheet
Dim wks_array_eng() As Single
Dim wks_array_frf() As Single
Private Sub CommandButton_RunReports_Click()

Application.DisplayAlerts = False

Call fill_wks_array_eng
Call fill_wks_array_frf

transitlist.Activate
transitlist.Range("A3").Activate
Call run_transit(1)

End Sub
Private Sub run_transit(intcol1 As Integer)

    For Each c In ActiveCell.CurrentRegion.Cells
      If c.Column = intcol1 Then

        strReportTransit = Trim(c)
        Set objSheet = hierarchypull
        objSheet.Range("A2").Value = c
        TextBox_transitname.Value = CStr(hierarchypull.Range("A4").Value)

        Call OutputToFile(wks_array_eng, "E")

      End If

    Next
    
Application.DisplayAlerts = True

MsgBox ("Done")

control.Activate

End Sub
Public Sub OutputToFile(wks_array() As Single, rpt_lang As String)

Dim filename As String
Dim temp_num_sheets As Integer
Dim temp_indx As Integer

Select Case rpt_lang
    Case "E"
        filename = "Report Jan31,2011 - " & Trim(TextBox_transitname.Value) & " District" & " .xls"
End Select

Sheets(wks_array).Select
    
ActiveWindow.SelectedSheets.Copy

temp_num_sheets = Worksheets.Count


For temp_indx = 1 To temp_num_sheets
    Sheets(temp_indx).Activate
    ActiveSheet.Range("A1:CA2000").Copy
    ActiveSheet.Range("A1:CA2000").PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Protect Password:="asdf", DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.Range("C1").Select
Next temp_indx

ActiveWorkbook.Colors(38) = RGB(59, 90, 111)
ActiveWorkbook.Colors(39) = RGB(234, 234, 234)

Sheets(1).Activate

'ActiveSheet.Protect Password:="asdf", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.SaveAs "C:\temp\" & filename
        
Workbooks(filename).Close SaveChanges:=False
  
End Sub

Private Sub TextBox_transitname_Change()

End Sub

Public Sub fill_wks_array_eng()

Dim start_wks_indx As Integer
Dim end_wks_indx As Integer
Dim ArraySize As Integer
Dim counter As Integer
Dim array_indx As Integer

start_wks_indx = eng_reports.Index + 1
end_wks_indx = frf_reports.Index - 1

ArraySize = end_wks_indx - start_wks_indx

ReDim wks_array_eng(ArraySize)

array_indx = 0

For counter = start_wks_indx To end_wks_indx
    wks_array_eng(array_indx) = counter
    array_indx = array_indx + 1
Next

End Sub
Public Sub fill_wks_array_frf()

Dim start_wks_indx As Integer
Dim end_wks_indx As Integer
Dim ArraySize As Integer
Dim counter As Integer
Dim array_indx As Integer

start_wks_indx = frf_reports.Index + 1
end_wks_indx = data.Index - 1

ArraySize = end_wks_indx - start_wks_indx

ReDim wks_array_frf(ArraySize)

array_indx = 0

For counter = start_wks_indx To end_wks_indx
    wks_array_frf(array_indx) = counter
    array_indx = array_indx + 1
Next

End Sub

Open in new window

0
Comment
Question by:jk_12
2 Comments
 
LVL 3

Accepted Solution

by:
raiERB earned 250 total points
Comment Utility
To add another worksheet you can use the following line:

Excel.Worksheets.Add

I suggest to add it just befor the first "End Sub" (@ line 15) to avoid clashign with the rest of the macro.
0
 

Author Closing Comment

by:jk_12
Comment Utility
Thanks for the help.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now