Need a VBA Code to refresh data and save the active sheet as a new workbook.

**Mysore** · 09-22-2012, 10:23 PM

Hi All

I have set up an Ageing Statement in sheet1 which contains Invoice Listing for a customer. This data is pulled from Sheet2 and is extracted through Index Function. Based on the number of value in a specific Cell in Sheet 2, the data gets refreshed in Sheet1.

I need a VBA Code to do the following,

1) VBA code to generate the statement for each customers based on cell input in Sheet 2
2) Upon refreshing each statement, the active sheet needs to be saved a new file with pre-allocated name picking up from a specific range in the statement sheet.

Any help is much appreciated.

Thanks

Mysore

**protonLeah** · 09-23-2012, 01:31 AM

This is a pretty big request in one chunk. I'm sure you'll get better response if you break down your question into several parts, so that they can be tackled one after the other.

Also, we're happy to help you with your effort, but nobody here will be keen on developing a solution from scratch. This forum is about helping people learn how to use Excel, not a free development service.

Have you started any work on this project yet?
How far did you get?
Where are you stuck?

We can help you develop your own code, but if you want someone to code this for you all the way, you should try and find a commercial developer.

**Mysore** · 09-23-2012, 08:50 AM

Hi There,

I have currently build the below mentioned macros.
Baiscally the first macro would assign a counter to a specific cell(E4)and this macro needs to loop based on cell(E2)value ie. if E2 = 37, then this macro should run 37 times by assigning value from 1 until 37 to cell E4.

Upon assigning a a counter to Cell E4 in "Control" Sheet, the second macro would
unhide row in "Statment" Sheet and the data is refreshed.

Finally the activesheet ie "Statement" needs to be saved in excel 2003 Version.

The above 3 macros should be consolidated and run multiple times depending upon the
cell value in E2 in "Control Sheet".

Just need a little help to tweak the code and make it work.

Thanks
Krishna

Sub Choose_Cell()
' This macro assigns Counter to a specific cell and based
' on the value in the cell, the "Statement" Sheet data refreshes.

Sheets("Control").Activate
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "2"
End Sub



Sub Hide_unhide()
' This macro should trigger based on Choose_Cell macro and hide empty rows
Dim r As Range, cell As Range
Dim ws As Worksheet

Application.ScreenUpdating = False
    
For Each ws In ThisWorkbook.Worksheets
    If ws.Name = "Statement" Then
       Set r = ws.Range("B20:B71")
        For Each cell In r
            If cell.Value = "" Then
                cell.EntireRow.Hidden = True
            Else
                cell.EntireRow.Hidden = False
            End If
        Next cell
    End If
   
Next ws
End Sub
Sub Create_file()
' This macro should copy the refreshed data in "Statement" Sheet and save as an excel 2003 version file.
Sheets("Statement").Copy
    'Application.DisplayAlerts = False
    With ActiveSheet.UsedRange
        .Copy
        .PasteSpecial xlValues
        .PasteSpecial xlFormats
    End With
    Application.CutCopyMode = False
    'ActiveWorkbook.SaveAs "Full Path/" & Format(Range("b10"), "mmm-dd-yy" & "HHMM" & ".xls")
    ActiveSheet.SaveAs Filename:=Format(Range("b10") & ".xls")
    'Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.ScreenUpdating = True

End Sub