Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste formatting and values only
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste formatting and values only
Version of Excel is 2003
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste formatting and values only
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..... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste formatting and values only
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste formatting and values only
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and paste up to values in last row | Excel Discussion (Misc queries) | |||
Copy and paste values, formatting and formulas | Excel Discussion (Misc queries) | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
Copy/Paste Values | Excel Programming | |||
How do i compare values from two sheet and copy & paste if values match? | Excel Programming |