Hello,
I am brand new to VB and have run into a roadblock in writing a macro that will achieve my desired result. I apologize for any lack of clarity due to my unfamiliarity with this. I am using excel 2010
Users will answer 'yes' or 'no' to 8 different questions. If the answer is 'no' I would like for the corresponding pivot table to appear in specified locations (there is a separate pivot for each question, 8 total). The locations are B33, D33, F33 and H33. I would only like the pivot tables for the first 4 'no’s' to appear.
Examples of possible results:</SPAN>
<TBODY>
</TBODY>-OR-
<TBODY>
</TBODY>I have recorded macros of copy and pasting the various options for specified pivot table locations. I have then tried to create a IF_ElseIf_Else macro to automate the action. Below is the code I have so far. Any help that can be offered is much appreciated. If there is an easier way to make this happen, that would be great too!
Thanks,
Luke </SPAN>
<TBODY>
</TBODY>
I am brand new to VB and have run into a roadblock in writing a macro that will achieve my desired result. I apologize for any lack of clarity due to my unfamiliarity with this. I am using excel 2010
Users will answer 'yes' or 'no' to 8 different questions. If the answer is 'no' I would like for the corresponding pivot table to appear in specified locations (there is a separate pivot for each question, 8 total). The locations are B33, D33, F33 and H33. I would only like the pivot tables for the first 4 'no’s' to appear.
Examples of possible results:</SPAN>
No-1</SPAN> | No-2</SPAN> | No-3</SPAN> | No-4</SPAN> |
Pivot-1</SPAN> | Pivot-2</SPAN> | Pivot-5</SPAN> | Pivot-6</SPAN> |
<TBODY>
</TBODY>
No-1</SPAN> | No-2</SPAN> | No-3</SPAN> | No-4</SPAN> |
P-3</SPAN> | P-4</SPAN> | P-7</SPAN> | P-8</SPAN> |
<TBODY>
</TBODY>
Thanks,
Luke </SPAN>
Sub IF_ELSEIF_ELSE_FUNCTION() If Worksheets("Calcs").Range("D19").Formula = Worksheets("Calcs").Range("A14") Then Sub CoachesD_1() ' ' CoachesD_1 Macro ' ' Sheets("Pivots").Select Range("A2:A4").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("B33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D20") = Worksheets("Calcs").Range("A15") Then Sub LeadsD_1() ' ' LeadsD_1 Macro ' ' Sheets("Pivots").Select Range("B2:B3").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("B33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D21") = Worksheets("Calcs").Range("A16") Then Sub AchievesD_1() ' ' AchievesD_1 Macro ' ' Sheets("Pivots").Select Range("C2:C4").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("B33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D22") = Worksheets("Calcs").Range("A17") Then Sub CommunicatesD_1() ' ' CommunicatesD_1 Macro ' ' Sheets("Pivots").Select Range("D2:D3").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("B33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D23") = Worksheets("Calcs").Range("F14") Then Sub PutsD_1() ' ' PutsD_1 Macro ' ' Sheets("Pivots").Select Range("E2:E3").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("B33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D24") = Worksheets("Calcs").Range("F15") Then Sub ProvidesD_1() ' ' ProvidesD_1 Macro ' ' Sheets("Pivots").Select Range("F2:F3").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("B33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D25") = Worksheets("Calcs").Range("F16") Then Sub ExecutesD_1() ' ' ExecutesD_1 Macro ' ' Sheets("Pivots").Select Range("G2:G3").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("B33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D26") = Worksheets("Calcs").Range("F17") Then Sub ChangeD_1() ' ' ChangeD_1 Macro ' ' Sheets("Pivots").Select Range("H2:H4").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("B33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D28") = Worksheets("Calcs").Range("A15") Then Sub LeadsD_2() ' ' LeadsD_2 Macro ' ' Sheets("Pivots").Select Range("B2:B3").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("D33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D29") = Worksheets("Calcs").Range("A16") Then Sub AchievesD_2() ' ' AchievesD_2 Macro ' ' Sheets("Pivots").Select Range("C2:C4").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("D33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D30") = Worksheets("Calcs").Range("A17") Then Sub CommunicatesD_2() ' ' CommunicatesD_2 Macro ' ' Sheets("Pivots").Select Range("D2:D3").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("D33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D31") = Worksheets("Calcs").Range("F14") Then Sub PutsD_2() ' ' PutsD_2 Macro ' ' Sheets("Pivots").Select Range("E2:E3").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("D33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D32") = Worksheets("Calcs").Range("F15") Then Sub ProvideD_2() ' ' ProvideD_2 Macro ' ' Sheets("Pivots").Select Range("F2:F3").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("D33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D33") = Worksheets("Calcs").Range("F16") Then Sub ExecutesD_2() ' ' ExecutesD_2 Macro ' ' Sheets("Pivots").Select Range("G2:G3").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("D33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D34") = Worksheets("Calcs").Range("F17") Then Sub ChangeD_2() ' ' ChangeD_2 Macro ' ' Sheets("Pivots").Select Range("H2:H4").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("D33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D37") = Worksheets("Calcs").Range("A16") Then Sub AchievesD_3() ' ' AchievesD_3 Macro ' ' Sheets("Pivots").Select Range("C2:C4").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("F33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D38") = Worksheets("Calcs").Range("A17") Then Sub CommunicatesD_3() ' ' CommunicatesD_3 Macro ' ' Sheets("Pivots").Select Range("D2:D4").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("F33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D39") = Worksheets("Calcs").Range("F14") Then Sub PutsD_3() ' ' PutsD_3 Macro ' ' Sheets("Pivots").Select Range("E2:E4").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("F33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D40") = Worksheets("Calcs").Range("F15") Then Sub ProvideD_3() ' ' ProvideD_3 Macro ' ' Sheets("Pivots").Select Range("F2:F4").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("F33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D41") = Worksheets("Calcs").Range("F16") Then Sub ExecutesD_3() ' ' ExecutesD_3 Macro ' ' Sheets("Pivots").Select Range("G2:G4").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("F33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D42") = Worksheets("Calcs").Range("F17") Then Sub ChangeD_3() ' ' ChangeD_3 Macro ' ' Sheets("Pivots").Select Range("H2:H4").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("F33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D46") = Worksheets("Calcs").Range("A17") Then Sub CommunicatesD_4() ' ' CommunicatesD_4 Macro ' ' Sheets("Pivots").Select Range("D2:D4").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("H33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D47") = Worksheets("Calcs").Range("F14") Then Sub PutsD_4() ' ' PutsD_4 Macro ' ' Sheets("Pivots").Select Range("E2:E4").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("H33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D48") = Worksheets("Calcs").Range("F15") Then Sub ProvideD_4() ' ' ProvideD_4 Macro ' ' Sheets("Pivots").Select Range("F2:F4").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("H33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D49") = Worksheets("Calcs").Range("F16") Then Sub ExecutesD_4() ' ' ExecutesD_4 Macro ' ' Sheets("Pivots").Select Range("G2:G4").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("H33").Select ActiveSheet.Paste End Sub ElseIf Worksheets("Calcs").Range("D50") = Worksheets("Calcs").Range("F17") Then Sub ChangeD_4() ' ' ChangeD_4 Macro ' ' Sheets("Pivots").Select Range("H2:H4").Select Selection.Copy Sheets("Career Path and Dev. Plan").Select Range("H33").Select ActiveSheet.Paste End Sub |
<TBODY>
</TBODY>