Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

moving between workbooks and worksheets and doing something

0

I have run this code from the sheet("dataeachstore") and the data is emptied in the desired ranges.  However, if I copy the code to another sheet("dashboard"} and put it in command button 1 in the "dashboard", and command button 1 is run, the data is not deleted.  The msgbox's do display, so the program is going through the With/End With path, but the data is not deleted.  It seems that the code runs but the sheet "DATASHEET" is not active or is not being accessed.

I guess I need code that I can , while being in a workbook/worksheet - go to another workbook/worksheet and do something and return to the initial workbook/worksheet.

Thanks  Carroll

Private Sub CommandButton1_Click()
With Workbooks("BEST_TEMPLATE_for_FINAL_REPORT_Ver_20_DATA_ALL_STORES.xlsm").Sheets("DATAEACHSTORE")
' EMPTYS THE QUERY DATA FOR EACH STORE - READY FOR NEW QUERY DATA
    'EMPTY RICHARDSON QUERY DATA
    Range("AZ6:BG3000").Select
    Selection.Clear
    Selection.Clear
    Range("AZ6:BG6").Select
    MsgBox "RICHARDSON QUERY DATA DELETED ", , "Example"
    
     'EMPTY DALLAS QUERY DATA
    Range("BV6:CC3000").Select
    Selection.Clear
    Selection.Clear
    Range("BV6:CC6").Select
   MsgBox "DALLAS QUERY DATA DELETED ", , "Example"
     'EMPTY FRISCO QUERY DATA
    Range("CR6:CY3000").Select
    Selection.ClearContents
    Selection.ClearFormats
     Range("CR6:CY6").Select
    MsgBox "FRISCO QUERY DATA DELETED ", , "Example"
     'EMPTY McKINNEY QUERY DATA
    Range("DN6:DU3000").Select
    Selection.ClearContents
    Selection.ClearFormats
    Range("DN6:DU6").Select
    MsgBox "McKINNEY QUERY DATA DELETED ", , "Example"
    
End With
    
End Sub
Answer
Discuss

Answers

0
Selected Answer

Hello Carrol,

Each command within the With block must be connected to the With statement that precedes it. Consider the difference.

Workbooks("BEST_TEMPLATE_for_FINAL_REPORT_Ver_20_DATA_ALL_STORES.xlsm").Sheets("DATAEACHSTORE").Range("AZ6:BG3000").Select

vs

Range("AZ6:BG3000").Select

The first example specifies the workbook and the worksheet and the Range. In the second example only the range is specified. When there is such lack of specification VBA simply assumes the ActiveSheet to be addressed, and this is what is happening when you run the code from the Dashboard. It deletes a lot of ranges from your Dashboard worksheet, or whichever worksheet happens to be active, and might damage it beyond repair.

The With statement saves you a lot of typing because you can connect any command within it to its first line. The point is that you can, but must not. In your code the choice was made not to connect.

    With Workbooks("BEST_TEMPLATE_for_FINAL_REPORT_Ver_20_DATA_ALL_STORES.xlsm").Sheets("DATAEACHSTORE")
        'EMPTY RICHARDSON QUERY DATA
        Range("AZ6:BG3000").Select
        Selection.Clear
        Selection.Clear           ' there is no need for this repetiton
        Range("AZ6:BG6").Select

If you wish to specify that the selected ranges should all be on the worksheet specified in the With statement, each selection command must be preceded by a period. The leading period will tell VBA that the command is incomploete by itself and needs to take the missing information from the With statement.

    With Workbooks("BEST_TEMPLATE_for_FINAL_REPORT_Ver_20_DATA_ALL_STORES.xlsm").Sheets("DATAEACHSTORE")
        'EMPTY RICHARDSON QUERY DATA
       .Range("AZ6:BG3000").Select
        Selection.Clear
       .Range("AZ6:BG6").Select

Note that the ClearContents command will delete only values but leave formatting intact. The Clear command will delete everything, including formatting, and reset the cells to Excel defaults.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login