Combining multiple then statements when there is one IF statement

Question

Basically it's a continuation of previously asked question (ref: How to copy all sheet names to a particular sheet in the same workbook?) for which I got my answer but after adding one more statements I stuck with two problems: 1) With current statements' order it shows only as if it executed code for one sheet and not for columns but sometimes when I try to mix If statements it works which is weird. So the question is: Does the order matter here and if yes in which order if should come? 2) Since IF statements are all the same for all thens I would like to combine them but dont know if it's possible at all Here's code:

Sub passport_combining ()

With ThisWorkbook.Sheets("MainSheet")
    For Each wsheet In ThisWorkbook.Sheets
        If wsheet.Name <> "MainSheet" Then

            Set nextEntry_FTE_quantity = .Cells(.Rows.Count, "K").End(xlUp).Offset(1, 0)
            Set nextEntry_nonrecurring_expenses = .Cells(.Rows.Count, "S").End(xlUp).Offset(1, 0)
            Set nextEntry_initiative_type = .Cells(.Rows.Count, "Q").End(xlUp).Offset(1, 0)
            Set nextEntry = .Cells(.Rows.Count, "G").End(xlUp).Offset(1, 0)

            If IsError(Application.Match(wsheet.Name, .Range("G:G"), 0)) Then nextEntry.Value = wsheet.Name
            If IsError(Application.Match(wsheet.Name, .Range("G:G"), 0)) Then nextEntry_FTE_quantity.Value = wsheet.Range("BH16").Value
            If IsError(Application.Match(wsheet.Name, .Range("G:G"), 0)) Then nextEntry_initiative_type.Value = wsheet.Range("K8").Value
            If IsError(Application.Match(wsheet.Name, .Range("G:G"), 0)) Then nextEntry_nonrecurring_expenses.Value = wsheet.Range("BH17").Value


        End If
    Next wsheet
End With

Thanks!

Can you clarify point 1? Not sure what you mean by it. As far as point 2, look at the multiple-line syntax of [`If...Then...End If`](https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/using-ifthenelse-statements). — BigBen, Jan 24 '19 at 19:04
For instance I have 11 Sheets in My WorkBook, I need to get Names of 10 sheets to 11th sheet and it needs to be in the last row + 1 of the column G, I already solved this problem in the link mentioned above. Apart from the sheets' names I also need to retrieve some additional information to that 11th sheet and they also need to be in particular columns. So when I execute my code it runs through my workbook but works strange. Sometimes it adds this information in my main sheet (11th) sometimes it doesn't and it all depends on my statements' placement — Adren, Jan 24 '19 at 19:08
@MDoskarin You should step through your code line-by-line by pressing F8 and watch what it's doing (and see where it is going awry). — dwirony, Jan 24 '19 at 19:18
`If Then` has two valid formats: single line and multi line. You have examples of both in your code. [See here](https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/using-ifthenelse-statements) — chris neilsen, Jan 25 '19 at 01:17

alowflyingpig · Accepted Answer · 2019-01-30T22:51:48.637

0

Updated answer to loop through worksheets

The issue you may have encountered was not setting your variables. The below code will loop through ALL worksheets in your workbook excluding your worksheet "MainSheet"

I did add in Debug.Print wsheet.Name just to see the loop working in the immediate window. Feel free to delete this line.

Sub passport_combining()

    Dim wsheet As Worksheet

    For Each wsheet In ThisWorkbook.Sheets
            If wsheet.Name <> "MainSheet" Then

                Set nextEntry_FTE_quantity = Cells(Rows.Count, "K").End(xlUp).Offset(1, 0)
                Set nextEntry_nonrecurring_expenses = Cells(Rows.Count, "S").End(xlUp).Offset(1, 0)
                Set nextEntry_initiative_type = Cells(Rows.Count, "Q").End(xlUp).Offset(1, 0)
                Set nextEntry = Cells(Rows.Count, "G").End(xlUp).Offset(1, 0)

                If IsError(Application.Match(wsheet.Name, Range("G:G"), 0)) Then
                    nextEntry.Value = wsheet.Name
                    nextEntry_FTE_quantity.Value = wsheet.Range("BH16").Value
                    nextEntry_initiative_type.Value = wsheet.Range("K8").Value
                    nextEntry_nonrecurring_expenses.Value = wsheet.Range("BH17").Value
                End If

            End If
            Debug.Print wsheet.Name
    Next wsheet


End Sub

edited Jan 30 '19 at 22:51

answered Jan 25 '19 at 04:51

alowflyingpig

730
7
18

Tried this structure but for some reason it keeps adding only one instance of sheet name and other values when there should be 10+ of them – Adren Jan 25 '19 at 07:42
Thats correct. Your code and question didn't state anymore than 1 value.. It can be done if you have multiple. Where are the values stored? Can you provide anymore details. Cheers! – alowflyingpig Jan 29 '19 at 00:03
So I have certain amount of n Sheets (Sheet1,Sheet2...Sheetn) and one main sheet (let's call it Main) where I aggregate all the information I pull from the rest sheets. Each Sheet (apart from the main one) contains various type of information which need to be pulled (economics indicators, quantities etc) and they are all stored in the same cell address for all sheets. Usually these sheets have some unique "passport" name and first thing code needs to do is too check if the passport name already exists in the list and only then pull all necessary information contained in sheets – Adren Jan 30 '19 at 06:40
So, what you want is to loop through all worksheets in your workbook, but if the sheet name = "Mainsheet" then skip to next worksheet. Continue the loop till all worksheets have been looped through? If yes then this is very simple to achieve. – alowflyingpig Jan 30 '19 at 22:42
I know it is! It's just strange to me that when I run this code it executes it perfectly but sometimes it just adds information only for one sheet and doesn't do anything for the rest – Adren Jan 31 '19 at 05:22
@MDoskarin Do you have hidden sheets? – alowflyingpig Jan 31 '19 at 05:42
By the way the code in my answer above was ammended. Copy that and try. But also see if you have hidden sheets.. – alowflyingpig Jan 31 '19 at 05:43
Thank you, it works now! It's so strange that I needed only this "Debug.Print wsheet.name". Can you elaborate on what exactly it does? – Adren Jan 31 '19 at 07:24
@MDoskarin the issue would have been the `block` statments, your `if` `end if` and `for` `next` The `debug.print` code simply prints in the immedaite window - it really does nothing to the excutable code. It's a visual aid to see if the code/loops are doing what you want. press `ctrl + g` to bring up the immediate window. I suggest googling "immediate window" to see what it is used for and its application in problem solving. Im glad it works now tho. And appreciate the answers being ticked and marked as correct. Cheers!! – alowflyingpig Jan 31 '19 at 22:32

Combining multiple then statements when there is one IF statement

1 Answers1