[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Automate Reporting in Excel

Posted on 2011-03-02
2
Medium Priority
?
268 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
[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 3

Accepted Solution

by:
raiERB earned 750 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

649 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