Copy and paste formatting and values only

[Santa-D]

I have a workbook with a series of array formulas to sum up a large
amount of actuals and budget figures to generate a summary report.

such as:

{=SUM((act.ccb=VALUE('BA Report'!BCostCentre))*(act.ccc=VALUE('BA
Report'!$C19))*(act.ccd=VALUE('BA Report'!BFund))*(act.eb))*-1}

I have a column which has each BCostCentre that I need to report on -
(97 in total)

What I want to do is to filter the summary report on each individual
area, copy the summary report into a new workbook with the formatting
but only retain the values, and save the values based on the
description in a specfic cell.

Can this be easily done?

 

[Santa-D]

Version of Excel is 2003

 

[Santa-D]

I have created a template - "budget worksheet template.xla" to have
the values pasted into it.

The area that I am struggling with is trying to convert the logic into
VBA as I have little knowledge on VBA programming.... I definitley
need to do a course!


Obviously there needs to be a loop based on the array named
"loop.range", each value in the range (97 of them) will need to be
passed to the master summary sheet to update the values so that it can
be copied into the workbook.

I also want the worksheet to be named after the costcentre..... (the
Costcentre is the primary key for the loop.range and to generate the
summary report) ..... EG: - "2340105 - 08.09 Budget template.xls"

From other posts I have managed to determine that this portion of the
code will be something like....

<CODE>
....

Dim strSheetName as string

strSheetName = ActiveSheet.Range("BCostCentre") & " - 08.09 Budget
template.xls"

....
</CODE>


The new worksheet that has been opened will have the data from the
following copied and pasted from the master report.... (M. means
Master, N. means New Workbook)

From-To
M.E10:E11-N.E10:E11
M.D11-N.D11
M.G19:H788-N.G19:H788


So that I can be of further assistance the process would be:

1. Insert 1 x CostCentre code from the range into the field
BCostCentre on the Master worksheet
2. Recalculate values
3. Create new worksheet from template file "budget worksheet
template.xla"
4. Copy data ranges from master worksheet into new worksheet as values
5. Save new worksheet As "<BCostCentre> - 08.09 budget templates.xls"
into folder C:\Temptrial and close
6. Repeat steps 1-5 for Next CostCentre

Any assistance would be fantastic.....

 

[Santa-D]

Whilst there is no error checking and could be cleaned up but this is
what I managed to come up with and is working wonderfully! Except if
there is a \ character in the name of the region......

Sub CreateReports()

Dim i As Integer
Dim CCRange As Range
Dim fname As Variant
Dim bname As Variant


MyPath = "C:\TEMPTRIAL\REPORTS\"
ChDir MyPath

i = 0

' inititate loop in range
For Each c In Range("loop.range")

' Open budget template workbook
Workbooks.Add Template:="C:\TEMPTRIAL\budget worksheet template.XLT"

' return to 2008/09 Master Worksheet
Windows("2008-09 Budget Template - trial.xls").Activate
Worksheets("BA Report").Range("BCostCentre") = c.Value

Calculate

bname = "(" & Worksheets("BA
Report").Range("BCostCentreDescription").Value & ")"

Range("D10:E11").Select
Selection.Copy
Windows("budget worksheet template1").Activate
Range("D10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


Windows("2008-09 Budget Template - trial.xls").Activate

Range("G19:H735").Select
Selection.Copy
Windows("budget worksheet template1").Activate
Range("G19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

' BCostCentreDescription


fname = c.Value & " - " & bname & " - 08.09 BUDGET TEMPLATE" &
".xls"
ActiveWorkbook.SaveAs Filename:=fname, CreateBackup:=False
ActiveWorkbook.Close

Next c


End Sub

 

[Santa-D]

Made some final tweaks and this is the outcome of the final product.

Sub CreateReports()

Dim CCRange As Range
Dim fname As Variant
Dim bname As Variant

' Disable screenupdating to increase speed and minimise use of
' system memory

Application.ScreenUpdating = False

' make sure that excel does not calculate automatically
Application.Calculation = xlCalculationManual

' do not display any alerts - such as (overwrite existing file)
Application.DisplayAlerts = False




' location of where the reports will be saved
MyPath = "C:\TEMPTRIAL\REPORTS\"
' Change directory path to the location above
ChDir MyPath


' inititate loop in range
For Each c In Range("loop.range")

' Open budget template workbook
Workbooks.Add Template:="C:\TEMPTRIAL\budget worksheet template.XLT"

' return to 2008/09 Master Worksheet
Windows("2008-09 Budget Template - trial.xls").Activate

' select BA Report worksheet
Worksheets("BA Report").Activate
Worksheets("BA Report").Range("BCostCentre") = c.Value

' initiate workbook calculations
Calculate

' Obtain cost centre description to be included in the filename
bname = "(" & Worksheets("BA
Report").Range("BCostCentreDescription").Value & ")"


' insert desription of entity, cost centre and cost centre
description
Range("D10:E11").Select
Selection.Copy
Windows("budget worksheet template1").Activate
Range("D10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


' return to master template
Windows("2008-09 Budget Template - trial.xls").Activate

' select data from master template and paste into new work as values
Range("G19:H294").Select
Selection.Copy
Windows("budget worksheet template1").Activate
Range("G19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'return to top of sheet in template
Range("C15").Select


' create file name in the format of:
' 2340100 - Collection & Content Devel Div - 08.09 BUDGET
TEMPLATE.xls

fname = c.Value & " - " & bname & " - 08.09 BUDGET TEMPLATE" &
".xls"

' Save worksheet as the fiel name in the directory mentioned above
and close
ActiveWorkbook.SaveAs Filename:=fname, CreateBackup:=False
ActiveWorkbook.Close

' move to the next costcentre in the range
Next c

' Reenable screen updating & calculation
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

' reenable the display of alerts
Application.DisplayAlerts = True
' Prompt user on completion
MsgBox "Generation of Budget templates completed"


End Sub