hiding macro copy/paste activity

[ohexcell_ineedu]

Hey guys, I have a macro which copies and paste from one sheet to another.

Here is the code:

Sub ENRL_Bolt()

'Transfer format from ENRL_Bolt to Survey Data
    Application.ScreenUpdating = False
    Sheets("SurveyData").Select 'Set column height width of the SurveyData sheet
    Range("A1:BB1000").Select
    Selection.ColumnWidth = 2.29
    Selection.RowHeight = 15.75
    Range("J8").Select
    Sheets("ENRL_Bolt").Select
    ActiveWindow.SmallScroll Down:=-27
    Application.CutCopyMode = False
    Selection.Copy
    Application.CutCopyMode = False
    Range("A62:AE92").Select
    Selection.Copy
    Sheets("SurveyData").Select
    Range("A1").Select
    ActiveSheet.Paste
    Rows("3:3").Select
    Selection.RowHeight = 3.75
    Rows("1:2").Select
    Range("A2").Activate
    Selection.RowHeight = 13.5
    Range("A4:AE31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A32").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=27
    Range("A60").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=36
    Range("A88").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Rows("4:280").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("V8:W8").Select
    Range("A1").Select
    
    
    'Transfer column format from ENRL_Bolt sheet to Template sheet

    Sheets("ENRL_Bolt").Select
    Range("A206:AE207").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Template").Select
    Range("A62:AE63").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=39
    Range("A110:AE111").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=39
    Range("A158:AE159").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=57
    Range("A206:AE207").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=-129
    Sheets("ENRL_Bolt").Select
    Application.CutCopyMode = False
    Range("A209:AE236").Select
    ActiveWindow.SmallScroll Down:=-156
    Range("A65:AE92").Select
    Selection.Copy
    Sheets("Template").Select
    ActiveWindow.SmallScroll Down:=-15
    Range("A65:AE92").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=54
    Range("A113:AE140").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=57
    Range("A161:AE188").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=60
    Range("A209:AE236").Select
    ActiveSheet.Paste
    Range("Z215:AA215").Select
    Range("A1").Select
    Sheets("Start").Select
    
End Sub

I then added this at the start of it to hide the activity of the macro

Application.ScreenUpdating = False

but one thing you will see at the end of the macro is

Range("A1").Select

and the purpose of that was so that the sheet returned to the top, so that when you select the newly formatted sheet it wasn't at the very bottom of the page.

but now with the code hiding the activity it no longer does that..... does anyone know why? or will i just possibly have to go without the screenupdating code?

 

[Michael M]

you need to put

Application.ScreenUpdating = True

at the end of the macro
Also you need to remove the Select.Selection parts of your code

 

[ohexcell_ineedu]

thanks for the reply michael, but what do you mean by remove the select/selection parts of my code..... because it is all throughout the code.

this was just a recorded macro.....

 

[Michael M]

It simply makes it easier to Debug, it is faster, and more efficient
for example, this

Sheets("ENRL_Bolt").Select
    Range("A206:AE207").Select
    Application.CutCopyMode = False
    Selection.Copy

would become this

Sheets("ENRL_Bolt").Range("A206:AE207").Copy

 

[ohexcell_ineedu]

hehe..... i was half embarrassed posting my code up here, knew there would be a smarter way to do it.

thanks a lot for helping and teaching me!!

 

[Michael M]

No need to be embarrassed, using the recorder is a great way to get the job done, without too much effort.
Cleaning up the recorded code, is simply the next step in the VBA learning curve