Hi All-
I'm an experienced SQL guy, trying to get up to speed on VBA. Having a hard time getting through the documentation, so bear with me!
I have a frequently-used XLS template that I manually "finalize" before sending off to my colleague. The process has five steps:
1. Remove non-numeric cells (i.e. "empty" cells that contain formulas) from a data set
2. Change the range values on a graph to match the new data
3. copy/paste values on an entire sheet to remove formulas
4. paste the sheet into a new workbook
5. apply theme formatting to the new workbook
Here is my effort at VBA scripting. It throws all sorts of errors. I've also attached the workbook for your reference. The last three tabs are the relevant ones. Help is appreciated!
Sub CE_Finalize()
Application.ScreenUpdating = False
Dim rngUV As Range
Dim rngImp As Range
Dim rngx As Range
'clears non-numeric cells
ActiveWorkbook.Sheets("Flighting").Range("B:D").SpecialCells(xlCellTypeFormulas, xlTextFormat).ClearContents
're-defines the graph range
With ActiveWorkbook.Sheets("Flighting")
Set rngx = Range("B12", Range("b12").End(xlDown))
Set rngUV = Range("c12", Range("c12").End(xlDown))
Set rngImp = Range("d12", Range("d12").End(xlDown))
End With
With Sheets("Dashboard").ChartObjects("Chart 3")
SeriesCollection(1).XValues = rngx
SeriesCollection(1).Values = rngUV
SeriesCollection(2).Values = rngImp
End With
'copy/paste as values
With ActiveWorkbook.Sheets("Dashboard").Cells
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
With ActiveWorkbook.Sheets("Demographics").Cells
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
With ActiveWorkbook.Sheets("Flighting").Cells
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
'copy to new workbook
Dim oldwb As Workbook
Dim newwb As Workbook
Set oldwb = ActiveWorkbook
oldwb.Sheets("Flighting").Copy
Set newwb = ActiveWorkbook
oldwb.Sheets("Demographics").Copy Before:=newwb.Sheets(1)
oldwb.Sheets("Dashboard").Copy Before:=newwb.Sheets(2)
'apply theme to new workbook
newwb.ApplyTheme ("c:\data_dump\MY Theme.thmx")
Application.ScreenUpdating = True
End Sub
Bookmarks