Basic macro

Resolved · Low Priority · Version 2010

Tue 6 Sep 2016, 10:05 Delegate Richard said...

Richard has attended:
Excel Intermediate course
Excel Advanced course

Basic macro

I am trying to create a macro that takes a hard coded snapshot of data from three worksheets in a file and I keep getting a run time error. I can't see what i am doing wrong, it seems a straightforward macro. The error is 'run time error '9' subscript out of range. Could you assist?

Tue 6 Sep 2016, 11:05 Trainer Jens said...

RE: Basic macro

Hi Richard,

Thank you for the forum question.

To help you I will need to see the code.

Please copy the code and forward it in the forum.

Thank you.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Wed 7 Sep 2016, 07:44 Delegate Richard said...

RE: Basic macro

Here is the script. From reading forums I reckon the issue is that excel wants to name the new sheet created as 'sheet x' x being the next available sheet number whereas the macro is looking for 'sheet 5', so I need to add something that creates a name for the sheet as it is being created?

Sub Application_Output()
'
' Application_Output Macro
' Hardcode copy of the outputs of north and south application and summary outputs sheet containing the Access Fee and Tariff
'
' Keyboard Shortcut: Ctrl+Shift+F
'
Sheets.Add
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "Output"
Sheets("Output sheet").Select
Range("B2:E17").Select
Selection.Copy
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Columns("D:D").ColumnWidth = 25.29
Columns("E:E").ColumnWidth = 21.14
Cells.Select
Cells.EntireRow.AutoFit
Cells.EntireColumn.AutoFit
Sheets.Add
Sheets("Sheet6").Select
Sheets("Sheet6").Name = "NB snapshot"
Sheets("Trainpath request NB").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("NB snapshot").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets.Add
Sheets("Sheet7").Select
Sheets("Sheet7").Name = "SB snapshot"
Sheets("Trainpath request SB").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SB snapshot").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
Sheets("Output").Select
Range("B2").Select
End Sub

Wed 7 Sep 2016, 17:46 Edited on Thu 8 Sep 2016, 10:58 Trainer Mike said...

RE: Basic macro

After reviewing what you would like to do may I suggest an alternative method. This being to copy the sheet to a newsheet and rename the receiving sheet to name of choice. This will save having to reformat column widths, row height and all other formatting and then by using as you have done cells.select etc copy, paste special value you then get you snapshot desired.

Please review code as I have also added deleting snapshot sheets before adding so can if wish to repeat snapshots again and again as you update originals.

Just to let you know I have tested this in an example file and all worked but at present unable to attach file as do not have access to do so.

Enjoy

Mike Hodkinson

Code for this is

Sub CreateSnapshot()
'Created by Mike H for STL 07/09/16

'Uses sheet copy command which includes to copy after the last sheet in file
' via counting the number of sheets in file and inserting after that number.
' then renames sheet to name of choice

'This is repeated twice more for the other two snapshot sheets

'Code line re Screenupdating = false speeds up macro as screen does not have to refresh between transactions
'NB remember at end of code to switch it back on by = True as per below

Application.ScreenUpdating = False

'Checks / deletes previous snapshot sheets of these names
' This enables a subsequent snapshot to be taken again and again

On Error Resume Next
Application.DisplayAlerts = False

Sheets("Output").Delete
Sheets("NB snapshot").Delete
Sheets("SB snapshot").Delete

Application.DisplayAlerts = True
On Error GoTo 0

'Copy sheet code and renaming and copy paste special value
Sheets("Output sheet").Select
Sheets("Output sheet").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Output"

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False

Sheets("Trainpath request NB").Select
Sheets("Trainpath request NB").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "NB snapshot"

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False

Sheets("Trainpath request SB").Select
Sheets("Trainpath request SB").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "SB snapshot"

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False

'Save workbook and select Output sheet cell B2
ActiveWorkbook.Save
Sheets("Output").Select
Range("B2").Select

'Screen refresh turned back on
Application.ScreenUpdating = True

'Enjoy!

End Sub