Manually changing specific items in code

[bunty_1244]

Hi Experts,

I have recorded different macros which does following tasks for the same number of items, for example let’s call this items as A, B & C

Macro-1:- Selects/deselects few items

Example Code:-
ActiveSheet.PivotTables("PV-2").PivotFields("Brand").CurrentPage = "(All)"
With ActiveSheet.PivotTables("PV-2").PivotFields("Brand")
.PivotItems("A").Visible = False
.PivotItems("B").Visible = False
.PivotItems("C").Visible = False
End With

Macro-2: Highlights the same items
Example Code:- (For A only)

Cells.Find(What:="A", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 6662349
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Macro-3: Enters this items in to a cell

Example Code:-

Range("T2").Value = "A"
Range("T3").Value = "B"
Range("T4").Value = "C"

Macro-4: Finds and paste values of this items

Example Code:- (For A only)

Cells.Find(What:="A", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 7).Select
Selection.Copy
Sheets("Slide-14").Select
Range("X6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Macro-5 Deleting Entire row

Example Code:- (For A only)

Cells.Find(What:="A", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp


Now that, the challenge is the specific list of items may change/add or remove items in future and each time I have to find this items in code and manually change it. Is there a way through which I can place a list of this items in some sheet and before running macro I just update the list and based on that the macro perform all this tasks above? So basically I want my macro to look at the updated list first and take those items only to perform above operations?

Sorry if I have thought too long or I have not used proper terms, I have very basic knowledge of VBA and I was just wondering if there is some program for this.

I really appreciate your time.

Best,
Bunty

[jomili]

Not sure if this is what you're looking for but you can declare A, B, and C as String and use them in your code as shown below. If you want to use a list to fill the values of A, B, and C you could do it by replacing (for instance)"Yes" with something like "Activesheet.range("X14").value"

Please Login or Register  to view this content.

[mikeTRON]

You might want to look into LOOPing. That is what you are trying to do.

[bunty_1244]

Hi Jomilli,

Thanks you so much for your reply. I tried your suggestion and I see an error message saying that "User-defined type not defined". I have attached the image for better idea.

Test.png
Please advice.

Mike -I have separate macros for these tasks and also I have basic knowledge of VBA hence cant think of loop at this stage If I could just reference the names from sheets to my code (Wherever it is using) that would be great.

Again, thank you both of you for your time. I really appreciate it.

Best,
Bunty

[mikeTRON]

I found this helpful when I was learning.
http://www.excel-pratique.com/en/vba.php

Also for your error you typed SRTING instead of STRING.

[jomili]

That's the best type of error, when you've just mispelled something. Easily fixed.