Run Macro in Different sheets

[RAMONTER]

Hi All,

This is my first post but I have been reading as an unregistered user for the last don't know maybe couple of years... anyway hello to everyone.

This is my question.

I have a macro to copy certain cells from one sheet to another one, and I have two sets of ten sheets I have to analyse and copy-paste from-to... So, I guess my problem is the macro is using the sheets names and some solutions suggested by other users in different posts are not working.

I am copying here below the code...

Sub macro()

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

'code to run the next thing on several worksheets and minimize the number of lines

'I exclude certain sheets

If ws.Name <> "MENU" Or ws.Name <> "COPY-PASTE-SHEET-FAME" Or ws.Name <> "ANALYSIS" Or ws.Name <> "BRIDGE ME QTR TOTALS" Or ws.Name <> "BRIDGE SITE 1" Or ws.Name <> "BRIDGE SITE 2" Or ws.Name <> "BRIDGE SITE 2" Or ws.Name <> "BRIDGE SITE 3" Or ws.Name <> "BRIDGE SITE 4" Or ws.Name <> "BRIDGE SITE 5" Or ws.Name <> "BRIDGE SITE 6" Or ws.Name <> "BRIDGE SITE 7" Or ws.Name <> "BRIDGE SITE 8" Or ws.Name <> "BRIDGE SITE 9" Or ws.Name <> "BRIDGE SITE 10" Or ws.Name <> "PIVOT TABLE Q1" Or ws.Name <> "COPY-PASTE-SHEET-FAME-IC" Or ws.Name <> "Version info" Then

' I Do my thing here

Sheets("ANALYSIS SITE 1").Select
Range("A3").Select

Do While ActiveCell.Value <> 0


Dim n
Dim count As Integer
Dim count1 As Integer

n = Range("K3").Value
count = 0
' Variable to store rows of sheet ANALYSIS SITE 1
count1 = 0
' Variable to store rows of sheet BRIDGE SITE 1

Sheets("ANALYSIS SITE 1").Select
Range("K3").Select
ActiveCell.Offset(count, 0).Select

Do While ActiveCell.Value <> 0
count = count + 1

If ActiveCell.Value = "RED" Then

Range("A2").Select
ActiveCell.Offset(count, 0).Select
Selection.Copy
Sheets("BRIDGE SITE 1").Select
Range("A51").Select
ActiveCell.Offset(count1, 0).Select
ActiveSheet.Paste

Sheets("ANALYSIS SITE 1").Select
Range("C2").Select
ActiveCell.Offset(count, 0).Select
Selection.Copy
Sheets("BRIDGE SITE 1").Select
Range("B51").Select
ActiveCell.Offset(count1, 0).Select
ActiveSheet.Paste

Sheets("ANALYSIS SITE 1").Select
Range("D2").Select
ActiveCell.Offset(count, 0).Select
Selection.Copy
Sheets("BRIDGE SITE 1").Select
Range("C51").Select
ActiveCell.Offset(count1, 0).Select
ActiveSheet.Paste

Sheets("ANALYSIS SITE 1").Select
Range("E2").Select
ActiveCell.Offset(count, 0).Select
Selection.Copy
Sheets("BRIDGE SITE 1").Select
Range("E51").Select
ActiveCell.Offset(count1, 0).Select
ActiveSheet.Paste

Sheets("ANALYSIS SITE 1").Select
Range("G2").Select
ActiveCell.Offset(count, 0).Select
Selection.Copy
Sheets("BRIDGE SITE 1").Select
Range("F51").Select
ActiveCell.Offset(count1, 0).Select
ActiveSheet.Paste

Sheets("ANALYSIS SITE 1").Select
Range("H2").Select
ActiveCell.Offset(count, 0).Select
Selection.Copy
Sheets("BRIDGE SITE 1").Select
Range("G51").Select
ActiveCell.Offset(count1, 0).Select
ActiveSheet.Paste

Sheets("ANALYSIS SITE 1").Select
Range("I2").Select
ActiveCell.Offset(count, 0).Select
Selection.Copy
Sheets("BRIDGE SITE 1").Select
Range("H51").Select
ActiveCell.Offset(count1, 0).Select
ActiveSheet.Paste

Sheets("ANALYSIS SITE 1").Select
Range("J2").Select
ActiveCell.Offset(count, 0).Select
Selection.Copy
Sheets("BRIDGE SITE 1").Select
Range("I51").Select
ActiveCell.Offset(count1, 0).Select
ActiveSheet.Paste

Sheets("ANALYSIS SITE 1").Select
Range("K2").Select
ActiveCell.Offset(count, 0).Select
Selection.Copy
Sheets("BRIDGE SITE 1").Select
Range("J51").Select
ActiveCell.Offset(count1, 0).Select
ActiveSheet.Paste

count1 = count1 + 1

End If

Sheets("ANALYSIS SITE 1").Select
Range("A3").Select
ActiveCell.Offset(aux, 0).Select

Loop

Loop


End If

Next ws

End Sub

Dont know if I have been clear enough so please apologies if not and please ask if you need more info

thanks in advance!!

Ramon

 

[Andrew Poulsom]

Welcome to Mr Excel.

Not a direct answer to your quesstion, but if you want to exclude sheets I think you need And instead of or in:

If ws.Name <> "MENU" Or ws.Name <> "COPY-PASTE-SHEET-FAME" Or ws.Name <> "ANALYSIS" Or ws.Name <> "BRIDGE ME QTR TOTALS" Or ws.Name <> "BRIDGE SITE 1" Or ws.Name <> "BRIDGE SITE 2" Or ws.Name <> "BRIDGE SITE 2" Or ws.Name <> "BRIDGE SITE 3" Or ws.Name <> "BRIDGE SITE 4" Or ws.Name <> "BRIDGE SITE 5" Or ws.Name <> "BRIDGE SITE 6" Or ws.Name <> "BRIDGE SITE 7" Or ws.Name <> "BRIDGE SITE 8" Or ws.Name <> "BRIDGE SITE 9" Or ws.Name <> "BRIDGE SITE 10" Or ws.Name <> "PIVOT TABLE Q1" Or ws.Name <> "COPY-PASTE-SHEET-FAME-IC" Or ws.Name <> "Version info" Then

 

[RAMONTER]

Hi Andrew, Thanks for posting! well I tried that command line before as it was suggested that way in a different post and it has worked in previous projects, anyway dont pretend to argue your suggestion since for sure you have more exp than me.... just question, since it is inside the logic of an "IF" then "OR" makes more sense as I exclude pages if the name is xxx OR yyy (??) repeat just a question since my base is C++ and fortran not VBa... (and not even expert base..) I will try in the future the and logic just for learning!

thanks again

 

[Andrew Poulsom]

Or would be correct for equals, but for not equals you need And. Otherwise, for example, if the sheet was named "MENU" it wouldn't be named "COPY-PASTE-SHEET-FAME" so the result of your Or test would be True.

 

[RAMONTER]

ahhh got your point... then the fact that it worked before was just a random event!!