-2

How do I avoid the select in VBA. Need to to have Main as visible and other sheets hidden. Below are the codes I am currently using:

    Sub Run_Historical()

'Application.ScreenUpdating = False
Dim skillit As String
Dim nowsever As String
Dim CMSReport As String
Dim username As String
Dim pword As String
Dim setdate As String
Dim reportlocation As String

'Clear Data

Sheets("CMSRaw").Select
Range("A2:BX1500").Select
Selection.ClearContents


Sheets("Reference").Select
skillit = Range("B6").Value
Range("B6").Select
Do While Not IsEmpty(ActiveCell.Value)
    nrow = ActiveCell.Row
    skillit = ActiveCell.Value
    nowserver = Range("C" & nrow).Value
    reportlocation = Range("H" & nrow).Value

Remaining of the codes are still having select sheets and cells which slows my PC as I am running a lot of application.

CMSReport = Range("D" & nrow).Value
    setdate = Range("G" & nrow).Value
    reportit = Range("D" & nrow).Value
    originaladdress = ActiveCell.Address

'Function CMSConn(sUserID As String, sPassword As String, sServerIP As String, sSkill As String, sDate As String, sReport)

        Call CSSConn2(username, pword, Range("C" & nrow).Value, skillit, setdate, reportit, reportlocation)
        originalsheets = ActiveSheet.Name
        pastecell = Range("F" & nrow).Value
        Sheets(Range("E" & nrow).Value).Select
        Range(pastecell).Select

        ActiveSheet.Paste
        Sheets(originalsheets).Select
        Range(originaladdress).Select
        Range("A" & nrow).Value = Now()
        ActiveCell.Offset(1, 0).Select


    If ActiveCell.Value = "" Then
    MsgBox "Done"
    ThisWorkbook.Activate
    Sheets("MainPage").Select
    Range("A1").Select
    Exit Sub
    End If
Loop
'MsgBox "CMS data has been extracted", vbOKOnly

Application.ScreenUpdating = False


End Sub
Community
  • 1
  • 1
MarcM
  • 1
  • 4
  • Why do you want to avoid "select" ? What have you tried ? Where are you stuck? – WNG Nov 22 '17 at 18:52
  • 1
    see: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Scott Craner Nov 22 '17 at 18:53
  • I haven't tried anything yet. But I just need to have some of the sheets hidden. – MarcM Nov 22 '17 at 18:54
  • You're using Application.Screenupdating incorrectly. At the start, you want to set it to FALSE. (For some reason you have it commented out). At the end you want to set it to TRUE. This will probably speed up your code faster than ditching the Selects. – jeffreyweir Nov 22 '17 at 19:04

1 Answers1

0

Simply refer to the worksheets/ranges directly, eg, instead of:

Sheets("CMSRaw").Select
Range("A2:BX1500").Select
Selection.ClearContents

Use:

Worksheets("CMSRaw").Range("A2:BX1500").ClearContents
YowE3K
  • 23,852
  • 7
  • 26
  • 40
Josh Eller
  • 2,045
  • 6
  • 11
  • @maldred My bad, I pasted the selected code twice and thought I edited the second one, but for whatever reason it didn't seem to take. I edited it for the original intent. – Josh Eller Nov 22 '17 at 19:12