Macro

[Marika]

I have a macro that's running on 1 worksheet, but would like to include certain other worksheets as well (Other worksheets has same data only diff. values)
TIA

 

[ajm]

wow. hopeless as i am, now that i jumped over the one hundred mark with my posts, i have become a board master. maybe they should change the criteria to 100 correct responses to other people's queries.

 

[Stromma]

Hi ajm

Did you use my code as is or just a part of it?

In your first code you hade a space in the beginning of: " Total"
and in the end of: " promo group"

Also make sure that you got the Sheets.Name right. In my test i changed the Sheets.Name to Blad1.

It worked fine for me.

/Roger

 

[ajm]

Roger,

I just tried your code exactly as its written, with the same sheet names as yours, and i still get the error.

could it be something to do with setting options explicit or anything else "outside" the actual sub end sub code.

 

[Stromma]

Hi

Ive just e-mailed you a copy of the testbook i used.

Check it out and see if it works.

/Roger

 

[ajm]

guess what. compile error. i am perplexed. i did email you a copy of the workbook i am working on. do you get the same error using my file?

 

[Scott Huish]

Try this:

Sub DeleteWords()
Dim ShArr(), Sh
ShArr = Array(Sheets("Coles"), Sheets("Woolworths"))
For Each Sh In ShArr
With Sh.Range("A:A")
.Replace What:="Total ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace What:=" promo group", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
Next Sh
End Sub

 

[ajm]

same response. Compile error: can't assign to array and highlights ShArr from line three: ShArr = Array(Sheets("Coles"), Sheets("Woolworths"))

Parry suggested the same : With Sh.Range("A:A") in his post on this topic. I am thinking i can do a workaround by grouping the sheets, selecting col A, and then setting the delete dogs loose.

Grouping the sheets comes back with :
Sheets(Array("Coles", "Woolworths")).Select

so, macro seems to be:

Sub Macro6()
'
' Macro6 Macro

Sheets(Array("Coles", "Woolworths")).Select
Sheets("Woolworths").Activate
Columns("A:A").Select
Selection.Replace What:="total", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Selection.Replace What:="promo group", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

End Sub

 

[ajm]

sorry, take out "Sheets("Woolworths").Activate" and it works fine.

 

[ajm]

here's an extra bit of the puzzle. can you autofilter on two sheets at once? i need to look for the same criteria on the two sheets and then delete those rows. its quickest on the one sheet using an autofilter macro, but this doesn't work with grouped sheets.

is there a workaround without doing one sheet at a time?

 

[Marika]

Sorry Ajm, but I am still confused on first question
I still can't seem to go to the next sheet..
Here is the code I use to subtotal my data (thanks to GaryB), it works well except at this stage it puts the results x3 below it self on same sheet?


Private Sub CommandButton2_Click()
Dim lngRow As Long
Dim ShArr(), Sh
ShArr = Array(Sheets("Sunday Night"), Sheets("Monday Morning"), Sheets("Tuesday Morning"))
For Each Sh In ShArr

lngRow = WorksheetFunction.Max(Range("a65536").End(xlUp).Row, Range("b65536").End(xlUp).Row)

Range("A" & lngRow + 2) = "Monday Total"
Range("B" & lngRow + 2).Formula = "=SumProduct(--(Weekday(L2:L" & lngRow & ") = 2))"
Range("A" & lngRow + 3) = "Tuesday Total"
Range("B" & lngRow + 3).Formula = "=SumProduct(--(Weekday(L2:L" & lngRow & ") = 3))"
Range("A" & lngRow + 4) = "Wednesday Total"
Range("B" & lngRow + 4).Formula = "=SumProduct(--(Weekday(L2:L" & lngRow & ") = 4))"
Range("A" & lngRow + 5) = "Thursday Total"
Range("B" & lngRow + 5).Formula = "=SumProduct(--(Weekday(L2:L" & lngRow & ") = 5))"
Range("A" & lngRow + 6) = "Friday Total"
Range("B" & lngRow + 6).Formula = "=SumProduct(--(Weekday(L2:L" & lngRow & ") = 6))"
Range("A" & lngRow + 7) = "Saturday Total"
Range("B" & lngRow + 7).Formula = "=SumProduct(--(Weekday(L2:L" & lngRow & ") = 7))"
Range("A" & lngRow + 8) = "Sunday Total"
Range("B" & lngRow + 8).Formula = "=SumProduct(--(Weekday(L2:L" & lngRow & ") = 1))"
Range("A" & lngRow + 9) = "Grand Total"
Range("B" & lngRow + 9).Formula = "=Sum(B" & lngRow + 2 & ":B" & lngRow + 8 & ")"
Range("A" & lngRow + 10) = "Duration Count"
Range("B" & lngRow + 10).Formula = "=Sum(J1:J" & lngRow & ")"
Range("B" & lngRow + 2, "L" & lngRow + 10).NumberFormat = "0"
Next Sh

End Sub

Please tell me what I am missing