Solved

Automate Reporting in Excel

Posted on 2011-03-02
2
259 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
ID: 35024524
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
ID: 35026289
Thanks for the help.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

810 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