You are here:

Excel/Run Macro from ActiveSheet Only

Advertisement


Question
Hello Aidan,

I need you help on fixing the below macro.

This macro copy cells from ATWS sheet to REPORT sheet if it satisfies the criteria, however I would like to run this macro to other sheets as well (I have ATWS1 to ATWS30), when I run it using the below macro it only reads the ATWS Sheet, can you assist me with this please? basically what I needed is to run the macro on the active sheet. Thank you so much in advance

Sub REPORTING()

Application.ScreenUpdating = False
Application.StatusBar = "Job Register Daily Report is currently updating, please wait."


'COPY ALL REPORT ITEMS TO "REPORT" SHEET
Sheets("REPORT").Select
With Sheets("REPORT")
   .UsedRange.Offset(6).ClearContents
   .UsedRange.Offset(6).ClearFormats
End With
Dim lr As Long, lr2 As Long, r As Long, ws1 As Worksheet, ws2 As Worksheet, targetWB As Workbook, wb2 As Workbook, N As Long
Set ws1 = Sheets("ATWS")
Set ws2 = Sheets("REPORT")

N = 6
Try = ActiveSheet.Cells(1, 1)
lr = ws1.Cells(Rows.Count, "Z").End(xlUp).Row
lr2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
test = ActiveSheet.Name

   For r = 6 To lr
       
       If ws1.Range("Z" & r).Value = Try Or ws1.Range("Z" & r).Value = "YES" Or ws1.Range("Z" & r).Value = "PO" Then

         ws1.Cells(r, 7).Copy
         ws2.Cells(N, 1).Select
         Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
         Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
         
         ws1.Cells(r, 11).Copy
         ws2.Cells(N, 2).Select
         Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
         Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
         
         ws1.Cells(r, 19).Copy
         ws2.Cells(N, 3).Select
         Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
         Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
         
         
         N = N + 1
       End If
   Next r
Sheets("REPORT").Select
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

Answer
You have coded the variable WS1 to refer to a specific sheet - change it to

Set ws1 = ActiveSheet

and it should then be fine.  The ONLY issue with this is it could be run on any sheet including the reporting one, so you may wish to check ws1.name to make sure it's an acceptable sheet
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.