Looping through sheets to perform code

you can do another loop with a counter it would look something like this

Sub ADDROW

Lastrow = 1000
counter = 1

while counter <= 5 'change this to the number of sheets that you are looking at
     sheets(counter).select
     For j = 5 To Lastrow
      If Cells(j, "P").Value = "ADD" Then
        Rows(j).Insert Shift:=xlDown
        Rows(j - 1).Copy Rows(j)
 
      End If
     Next
Wend
End Sub

it doesn't matter if the sheets have different names, think of it as they have assigned numbers, you can see the numbers when you push alt+f11 on the left side of the screen should list all of the sheets with labels like Sheet1(your sheet name) this is sheet1 your next is 2 and so forth.

I forgot to put in this line between next and wend

counter = counter + 1

This line will make it repeat until in my case sheet 5, sorry about that.

I appreciate the help. Can you help with one more code? I need to place the same steps into this formula too. I start by cycling through all of the sheets and "deleting" rows, then I go to the next code of "adding" rows (which you just helped me with.

Sub DELETEROWS()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    With ActiveSheet

        'Set the first and last row to loop through
        Firstrow = 5
        Lastrow = Cells(Rows.Count, "P").End(xlUp).Row
  
        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

            'We check the values in the P column in this example
            With .Cells(Lrow, "P")

                If Not IsError(.Value) Then

                    If .Value = "DELETE" Then .EntireRow.Delete
                    'This will delete each row with the Value "delete"
                    'in Column P, case sensitive.

                End If

            End With

        Next Lrow

    End With


    
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

Call Addrow()   

    
End Sub

---------- Post added at 10:49 PM ---------- Previous post was at 10:46 PM ----------

Originally Posted by shyguy107

Hello guys,
I am a medical professional in US. I am working on this excel sheet and am stuck , hence here.

For each patient , there are multiple dates of treatment and multiple dates of blood collection.

there are 3 lines of treatment for a given patient and 8 dates on which blood was collected.

I am trying to write a code to identify the date of blood collection closed to the treatment date. To that end, I am trying to write a code where for a given patient excel will compare the above two columns and calcuate the difference between
the dates of treatment and blood draw.

Since the no of blood collections exceeds treatments, there two columns are not in sync.

If anyone has any suggestions in this regards, I would appreciate it. I need excel to do this for the 250 patients that I have.

Thanks in advance for any suggestions.

Shyguy107

---------- Post added at 10:46 PM ---------- Previous post was at 10:42 PM ----------

[/COLOR]Try asking in a new post thread. Your question can be better answered by a range of people.

All you should need is the while statement that I added before.

Before your line that sets the variable at the top put

counter = 1

Right before your for statement put

While counter < 5 ' this is the number of sheets
Sheets(counter).select

Then after next put this

counter = counter+1
Wend

Follow what I did before and you should have it

I am on a mobile device so I cannot do it all, that is why I gave you tips. Tomorrow I will be able to help you more if you cannot get it. Just let me know.

---------- Post added at 09:56 PM ---------- Previous post was at 09:49 PM ----------

Shyguy,
Sambuka is right, please start a new thread for your question. if no one helps you, send me a message and I will look at it if I hadn't already.

Thanks

I think it is because it is all in the with activesheet and you are trying to switch pages. I am not sure why you have with activesheet

---------- Post added at 10:08 PM ---------- Previous post was at 10:05 PM ----------

Also make sure your last sheet is the last sheet in excels eyes. If you have moved the sheets around it may be that the first sheet looks like the last one. This is a small possibility, but could be it. I would think it is more due to the with activesheet.

Originally Posted by amotto11

I think it is because it is all in the with activesheet and you are trying to switch pages. I am not sure why you have with activesheet

---------- Post added at 10:08 PM ---------- Previous post was at 10:05 PM ----------

Also make sure your last sheet is the last sheet in excels eyes. If you have moved the sheets around it may be that the first sheet looks like the last one. This is a small possibility, but could be it. I would think it is more due to the with activesheet.

I dont think it had to do with the total number of sheets. I did it on the first code you helped me with and it worked fine.

It might have something to do with the activesheet. However, when I deleted the "With ActiveSheet" and "End With" I get a Compile Error on the ".Cells" reference. Any ideas?

---------- Post added at 11:33 PM ---------- Previous post was at 11:28 PM ----------

I think it has something to do with this. In the "AddRow" Code I establish Lastrow = 1000, For the "DeleteRow" I have this.

Lastrow = Cells(Rows.Count, "P").End(xlUp).Row

This looks to see where the last entry is in the row and counts back up. However, this is now out of the loop and cant find

---------- Post added at 11:33 PM ---------- Previous post was at 11:28 PM ----------

I just change the Lastrow to "lastrow = 1000". Still got the error, when I deleted the With "ActiveSheet"

It is because when you say with something. Your saying basically put this line in front of anything that follow until the end with. So whe you have .cells you need something in front of it. In front of anything with a .something put activesheet. This should fix your problem.

Sambuka, your code i believe would look like this

Sub DELETEROWS()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    
    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want

        'Set the first and last row to loop through
        Firstrow = 5
        Lastrow = Cells(Rows.Count, "P").End(xlUp).Row
        counter = 2
        
        While counter <= 24 ' this is the number of sheets
        Sheets(counter).Activate
        
        'We loop from Lastrow to Firstrow (bottom to top)
            For Lrow = Lastrow To Firstrow Step -1

            'We check the values in the P column in this example
                With ActiveSheet.Cells(Lrow, "P")

                    If Not IsError(.Value) Then

                        If .Value = "DELETE" Then .EntireRow.Delete
                    'This will delete each row with the Value "delete"
                    'in Column P, case sensitive.
                    End If
                End With
            Next Lrow
            counter = counter + 1
        Wend
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
End Sub

No problem, and thanks for the rep