Use IF dependent on Sheet Name to Call a VBA Function?

[justanotheruser]

Hi all,

I have some VBA code that I'm using for an Invoice system. As part of the system, it needs to call up a certain function based on the sheet name. I have two choices, I can either make 4 copies of the same code (when you press a button, called "Process Invoice", it runs a macro that runs a few different functions at the same time), and then just change VATCopy1, VATCopy2, VATCopy3, VATCopy4 and assign a different macro to each sheet, or use an IF formula.

So my code is:

Call VATCopy1 'This will do the VATCopy function and copy the relevant data to the "Database" sheet

Basically, I'm trying to do something that says:

• if Sheet Name is "VAT", run VATCopy1
• if Sheet Name is "VAT & Disc", run VATCopy2
• if Sheet Name is "Zero VAT", run VATCopy3
• if Sheet Name is "Zero VAT & Disc", run VATCopy4

Those are the four conditions I need to put in my code.

My attempt (for the first 2 sheets) is this:

Dim wsSheet As Worksheet
    For Each wsSheet In Worksheets
        If wsSheet.Name = "VAT" Then
            Call VATCopy1 'This will do the VATCopy function and copy the relevant data to the "Database" sheet from the sheet named "VAT"
        Else
        If wsSheet.Name = "VAT & Disc" Then
            Call VATCopy2 'This will do the VATCopy function and copy the relevant data to the "Database" sheet from the sheet named "VAT & Disc"
        End If

However this returns the Compile Error Block if without End if.

Can anyone please help?

Thanks in advance!

 

[VoG]

Try like this

Dim wsSheet As Worksheet
    For Each wsSheet In Worksheets
        If wsSheet.Name = "VAT" Then
            Call VATCopy1 'This will do the VATCopy function and copy the relevant data to the "Database" sheet from the sheet named "VAT"
        ElseIf wsSheet.Name = "VAT & Disc" Then
            Call VATCopy2 'This will do the VATCopy function and copy the relevant data to the "Database" sheet from the sheet named "VAT & Disc"
        End If

 

[justanotheruser]

Try like this

Dim wsSheet As Worksheet
    For Each wsSheet In Worksheets
        If wsSheet.Name = "VAT" Then
            Call VATCopy1 'This will do the VATCopy function and copy the relevant data to the "Database" sheet from the sheet named "VAT"
        ElseIf wsSheet.Name = "VAT & Disc" Then
            Call VATCopy2 'This will do the VATCopy function and copy the relevant data to the "Database" sheet from the sheet named "VAT & Disc"
        End If

Thanks for the fast response! Unfortunately, now I get a "For without Next" error - is this because all 4 conditions haven't been included? Thanks again.

 

[VoG]

You need to close the For loop

Dim wsSheet As Worksheet
    For Each wsSheet In Worksheets
        If wsSheet.Name = "VAT" Then
            Call VATCopy1 'This will do the VATCopy function and copy the relevant data to the "Database" sheet from the sheet named "VAT"
        ElseIf wsSheet.Name = "VAT & Disc" Then
            Call VATCopy2 'This will do the VATCopy function and copy the relevant data to the "Database" sheet from the sheet named "VAT & Disc"
        End If
    Next wsSheet

 

[niceguy_jay]

use this

Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
If wsSheet.Name = "VAT" Then
Call VATCopy1
ElseIf wsSheet.Name = "VAT & Disc" Then
Call VATCopy2
ElseIf wsSheet.Name = "Zero VAT" Then
Call VATCopy3
ElseIf wsSheet.Name = "Zero VAT & Disc" Then
Call VATCopy4
End If
Next wsSheet

 

[justanotheruser]

You need to close the For loop

Dim wsSheet As Worksheet
    For Each wsSheet In Worksheets
        If wsSheet.Name = "VAT" Then
            Call VATCopy1 'This will do the VATCopy function and copy the relevant data to the "Database" sheet from the sheet named "VAT"
        ElseIf wsSheet.Name = "VAT & Disc" Then
            Call VATCopy2 'This will do the VATCopy function and copy the relevant data to the "Database" sheet from the sheet named "VAT & Disc"
        End If
    Next wsSheet

Thanks, I added the other conditions to the code and ran the macro on the sheet called "VAT & Disc". It ran VATCopy2, but it also ran VATCopy3 and VATCopy4, which is very odd, perhaps I'm using the wrong operator?

Dim wsSheet As Worksheet
    For Each wsSheet In Worksheets
        If wsSheet.Name = "VAT" Then
            Call VATCopy1 'This will do the VATCopy function and copy the relevant data to the "Database" sheet from the sheet named "VAT"
        ElseIf wsSheet.Name = "VAT & Disc" Then
            Call VATCopy2 'This will do the VATCopy function and copy the relevant data to the "Database" sheet from the sheet named "VAT & Disc"
        ElseIf wsSheet.Name = "Zero VAT" Then
            Call VATCopy3 'This will do the VATCopy function and copy the relevant data to the "Database" sheet from the sheet named "Zero VAT"
        ElseIf wsSheet.Name = "Zero VAT & Disc" Then
            Call VATCopy4 'This will do the VATCopy function and copy the relevant data to the "Database" sheet from the sheet named "Zero VAT & Disc"
        End If
    Next wsSheet

Thanks again.

 

[RoryA]

Your code loops through all the worksheets, so if you have all those sheets, all 4 macros will be called.

 

[VoG]

Instead of looping

Select Case ActiveSheet.Name
    Case "VAT"
        Call VATCopy1
    Case "VAT & Disc"
        Call VATCopy2
    Case "Zero VAT"
        Call VATCopy3
    Case "Zero VAT & Disc"
        Call VATCopy4
End Select

 

[justanotheruser]

That worked, thank you