KathrynMcG
New Member
- Joined
- Jul 17, 2012
- Messages
- 5
Hi,
I am a total beginner to this, having only learnt about VBA in the last 24 hours.
However, this is what i'm trying to do:
We have a workbook with several worksheets. Each one represents a different area of our factory. Currently they are used for auditing purposes, with questions, and a score given and jobs listed to be fixed againt each question.
What we want to be able to do is to assign a code to each job (or row) on each worksheet (e.g. ENG for engineers) for who needs to fix it, and then have those jobs automatically populate on a different worksheet for each "person". So one for ENG jobs and QA jobs etc.
I googled it and borrowed some coding from another person who wanted a similar thing.
I have got it to work, but only for one worksheet, i.e. it will only search the sheet ("Lab & Offices") when I ultimately want it to seach all sheets.
Is there a way to define all sheets in workbook, or a range of sheets? Being a complete beginner, i'm sure it must be easy but i just don't know the correct coding for it.
This is what I have:
Option Explicit
Private Sub Worksheet_Activate()
Dim LR As Long
Me.UsedRange.Offset(1).ClearContents 'clear existing data
With Sheets("Lab & Offices")
.AutoFilterMode = False 'remove any prior filtering
.Rows(1).AutoFilter 'activate autofilter
.Rows(1).AutoFilter 6, ">=QA" 'filter column F for QA
LR = .Range("A" & .Rows.Count).End(xlUp).Row 'is any data visible?
If LR > 1 Then
.Range("A2:G" & LR).Copy Range("A2") 'copy any data visible to report
Else
Range("A2") = "no data found" 'if none, give that message
End If
.AutoFilterMode = False 'turn off autofilter
End With
End Sub
What I want to know is how to get it to look further than the "Lab & Offices" sheet (which is sheet 11). I want to look through sheets 2 to 11 for references to QA in column 6 (or F).
Appreciate any help!! (p.s. using Excel 2010)
I am a total beginner to this, having only learnt about VBA in the last 24 hours.
However, this is what i'm trying to do:
We have a workbook with several worksheets. Each one represents a different area of our factory. Currently they are used for auditing purposes, with questions, and a score given and jobs listed to be fixed againt each question.
What we want to be able to do is to assign a code to each job (or row) on each worksheet (e.g. ENG for engineers) for who needs to fix it, and then have those jobs automatically populate on a different worksheet for each "person". So one for ENG jobs and QA jobs etc.
I googled it and borrowed some coding from another person who wanted a similar thing.
I have got it to work, but only for one worksheet, i.e. it will only search the sheet ("Lab & Offices") when I ultimately want it to seach all sheets.
Is there a way to define all sheets in workbook, or a range of sheets? Being a complete beginner, i'm sure it must be easy but i just don't know the correct coding for it.
This is what I have:
Option Explicit
Private Sub Worksheet_Activate()
Dim LR As Long
Me.UsedRange.Offset(1).ClearContents 'clear existing data
With Sheets("Lab & Offices")
.AutoFilterMode = False 'remove any prior filtering
.Rows(1).AutoFilter 'activate autofilter
.Rows(1).AutoFilter 6, ">=QA" 'filter column F for QA
LR = .Range("A" & .Rows.Count).End(xlUp).Row 'is any data visible?
If LR > 1 Then
.Range("A2:G" & LR).Copy Range("A2") 'copy any data visible to report
Else
Range("A2") = "no data found" 'if none, give that message
End If
.AutoFilterMode = False 'turn off autofilter
End With
End Sub
What I want to know is how to get it to look further than the "Lab & Offices" sheet (which is sheet 11). I want to look through sheets 2 to 11 for references to QA in column 6 (or F).
Appreciate any help!! (p.s. using Excel 2010)
Last edited:
