condense VBA

**dantray02** · 03-11-2014, 01:02 PM

Hi, is there a way of making the following more efficient:

Basically, I am exporting a selection of pages from a workbook. THe selection is determined by the following

If Sheets("input sheet").Range("b47") = 2 Then

The value in B47 will either be a 0,1,2

2 - selects minimum range, plus two additional sheets
1 - selects minimum range, plus one additional sheet
0 - selects minimum range only.

the code is:

 If Sheets("input sheet").Range("b47") = 2 Then
    Sheets(Array("validation", "Cover Sheet", "Management Report", "Management Accounts", _
        "Balance Sheet", "Tax projection - Director 1", "Tax projection - Director 2")). _
        Select
    Selection.Activate
End If


'if one director is to receive a personal tax projection
If Sheets("input sheet").Range("b47") = 1 Then
    Sheets(Array("validation", "Cover Sheet", "Management Report", "Management Accounts", _
        "Balance Sheet", "Tax projection - Director 1")). _
        Select
    Selection.Activate
End If



If Sheets("input sheet").Range("b47") = 0 Then
    Sheets(Array("validation", "Cover Sheet", "Management Report", "Management Accounts", _
        "Balance Sheet")). _
        Select
    Selection.Activate
    End If

after which point a pdf is generated.

as always thank you in advance.

**Kaper** · 03-11-2014, 01:09 PM

You can start with

select case Sheets("input sheet").Range("b47")
  Case 2 
    Sheets(Array("validation", "Cover Sheet", "Management Report", "Management Accounts", _
        "Balance Sheet", "Tax projection - Director 1", "Tax projection - Director 2")). _
        Select
  Case 1 
    Sheets(Array("validation", "Cover Sheet", "Management Report", "Management Accounts", _
        "Balance Sheet", "Tax projection - Director 1")). _
        Select
  Case 0
    Sheets(Array("validation", "Cover Sheet", "Management Report", "Management Accounts", _
        "Balance Sheet")). _
        Select
End select
Selection.Activate

**TMS** · 03-11-2014, 01:15 PM

Maybe:

'if two directors are to receive a personal tax projection
If Sheets("input sheet").Range("b47") = 2 Then
    Sheets(Array("validation", "Cover Sheet", "Management Report", "Management Accounts", _
    "Balance Sheet", "Tax projection - Director 1", "Tax projection - Director 2")).Select
'if one director is to receive a personal tax projection
ElseIf Sheets("input sheet").Range("b47") = 1 Then
    Sheets(Array("validation", "Cover Sheet", "Management Report", "Management Accounts", _
    "Balance Sheet", "Tax projection - Director 1")).Select
ElseIf Sheets("input sheet").Range("b47") = 0 Then
    Sheets(Array("validation", "Cover Sheet", "Management Report", "Management Accounts", _
    "Balance Sheet")).Select
End If
Selection.Activate      ' ??? redundant ???

Regards, TMS

**Kaper** · 03-11-2014, 01:16 PM

Then next step could be:

dim sheetsstring as string
dim sheetslist
sheetsstring= "validation,Cover Sheet,Management Report,Management Accounts,Balance Sheet"
select case Sheets("input sheet").Range("b47")
  Case 2 
    sheetsstring = sheetsstring & ",Tax projection - Director 1,Tax projection - Director 2"
  Case 1 
    sheetsstring = sheetsstring & ",Tax projection - Director 1"
End select
sheetslist = split(sheetsstring,",")
Sheets(sheetlist).Select

**TMS** · 03-11-2014, 01:26 PM

@Kaper: that's neat. Do you need a Case Else for the "0" option?

And the double post is the forum, not you. Report the post to get it removed as a" forum induced double post"

Regards, TMS

**Kaper** · 03-11-2014, 05:48 PM

Hi,

Thanks for advice with double post.

As for case 0 (or case else) - I do not think is needed.
initial value of sheetsstring has already all needed sheet names.
Something has to be added to this string only in case 1 or 2.

**jindon** · 03-11-2014, 09:18 PM

I would write like this.

    Dim myArray, x As Long
    myArray = Array("validation", "Cover Sheet", "Management Report", "Management Accounts", _
        "Balance Sheet", "Tax projection - Director 1", "Tax projection - Director 2")
    x = Sheets("input sheet").Range("b47").Value
    If x Like "[12]" Then ReDim Preserve myArray(UBound(myArray) - x)
    Sheets(myArray).Select
    Selection.Activate

**TMS** · 03-11-2014, 09:29 PM

So, if x=2, you would reduce the upper bound by 2 ... . Feels wrong but I might be missing something.

I like the concept but maybe it should be -(2-x)

Regards, TMS

**jindon** · 03-11-2014, 10:11 PM

Ahhh, it was other way around... thanks.

Perhaps

    Dim myArray, x As Long
    myArray = Array("validation", "Cover Sheet", "Management Report", "Management Accounts", _
        "Balance Sheet", "Tax projection - Director 1", "Tax projection - Director 2")
    x = Sheets("input sheet").Range("b47").Value
    ReDim Preserve myArray(UBound(myArray) - 2 + x)
    Sheets(myArray).Select
    Selection.Activate

**dantray02** · 03-25-2014, 05:27 AM

Hi, sorry for the delay - I have been away for a week.

Can I please ask - if I needed to include an additional worksheet at the end of

     "Balance Sheet", "Tax projection - Director 1", "Tax projection - Director 2")

based on the same criteria how would I go about that?

I have tried

     "Balance Sheet", "Tax projection - Director 1", "Tax projection - Director 2", "Tax Calculations")

but this does not work.

thanks.
dan.

**TMS** · 03-25-2014, 05:45 AM

Well, the Director sheets are the optional ones, so they have to stay at the end.

Stick it in the middle somewhere before those Director items. I guess where depends on the sequence you want them printed.

Regards, TMS

**dantray02** · 03-25-2014, 06:13 AM

Hi,

The additional pages would also be optional. So if either director has a tax projection then i would also want to show the calculation at the end

thanks.

**TMS** · 03-25-2014, 06:27 AM

Then you need to look at your OP and adapt the code to reflect another variation.

The value in B47 will either be a 0,1,2

2 - selects minimum range, plus two additional sheets
1 - selects minimum range, plus one additional sheet
0 - selects minimum range only.

Regards, TMS

**snb** · 03-25-2014, 07:24 AM

Sub M_snb()
    With Sheets("input sheet").Range("b47")
      Sheets(Split("validation|Cover Sheet|Management Report|Management Accounts|Balance Sheet" & IIf(.Value > 0, "|Tax projection - Director 1" & IIf(.Value > 1, "|Tax projection - Director 2", ""), ""), "|")).Select
    End With
End Sub

condense VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

condense VBA

Re: condense VBA

Re: condense VBA

Re: condense VBA

Re: condense VBA

Re: condense VBA

Re: condense VBA

Re: condense VBA

Re: condense VBA

Re: condense VBA

Re: condense VBA

Re: condense VBA

Re: condense VBA

Re: condense VBA

Thread Information

Users Browsing this Thread

Similar Threads

Condense Information

Condense Data

Condense code

[SOLVED] Condense Code

Condense formula

Bookmarks

Bookmarks

Posting Permissions