Hi,
I am working trying to create a macro that will populate different worksheets based on a value in the dropdown selected in the tab called "List". I need the Macro to loop through all the values in the dropdown and populate different tabs based on the value. The name of the tab corresponds to the name in the dropdown.
For example: I select the item in the List tab called "MPL", the data in worksheets DataNoLOB, DataLOB and DataAllLOB will populate and I need to copy and paste all values into a tab called MPL. I should mention that the 3 tabs DataNoLOB, DataLOB and DataAllLOB are populated using Indirect formulas.
I have created the below macro so far, but it is pulling the same dropdown value for all sheets. What am I doing wrong?
I am working trying to create a macro that will populate different worksheets based on a value in the dropdown selected in the tab called "List". I need the Macro to loop through all the values in the dropdown and populate different tabs based on the value. The name of the tab corresponds to the name in the dropdown.
For example: I select the item in the List tab called "MPL", the data in worksheets DataNoLOB, DataLOB and DataAllLOB will populate and I need to copy and paste all values into a tab called MPL. I should mention that the 3 tabs DataNoLOB, DataLOB and DataAllLOB are populated using Indirect formulas.
I have created the below macro so far, but it is pulling the same dropdown value for all sheets. What am I doing wrong?
Code:
Sub loopthroughDropDownList()
'
' Macro11 Macro
'
'
Sheets("DataNoLOB").Select
Selection.Copy
Sheets("MPL").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("I16").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A573").Select
Sheets("DataLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MPL").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
Sheets("DataAllLOB").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("MPL").Select
Range("A723").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("DataNoLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CYB").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B6").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A572").Select
Sheets("DataLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CYB").Select
Range("A573").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("DataLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CYB").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=150
Range("M729").Select
Sheets("DataAllLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CYB").Select
Range("A723").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("IN").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("DataNoLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("IN").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G16").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "CY171"
Range("G16").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=6
Range("A573").Select
Sheets("DataLOB").Select
Selection.Copy
Sheets("IN").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("DataAllLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("IN").Select
Range("B576").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A723").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("List").Select
Range("A1").Select
Sheets("DataNoLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("A&E").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H19").Select
Range(Selection, Selection.End(xlDown)).Select
Sheets("DataLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("A&E").Select
Range("A573").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A574").Select
Range(Selection, Selection.End(xlDown)).Select
Sheets("DataAllLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("A&E").Select
Range("A723").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("DataNoLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("APL").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B15").Select
Range(Selection, Selection.End(xlDown)).Select
Sheets("DataLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("APL").Select
Range("A573").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-3
Range("G578").Select
Range(Selection, Selection.End(xlDown)).Select
Sheets("DataAllLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("APL").Select
Range("A723").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollWorkbookTabs Sheets:=-2
Sheets("DataNoLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("LP").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E15").Select
Range(Selection, Selection.End(xlDown)).Select
Sheets("DataLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("LP").Select
Range("A573").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A573").Select
Range(Selection, Selection.End(xlDown)).Select
Sheets("DataAllLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("LP").Select
Range("A723").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollWorkbookTabs Sheets:=-2
Sheets("DataNoLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Comm").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A10").Select
Range(Selection, Selection.End(xlDown)).Select
Sheets("DataLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Comm").Select
Range("A573").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(Selection, Selection.End(xlDown)).Select
Range("A572").Select
Range(Selection, Selection.End(xlDown)).Select
Sheets("DataAllLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Comm").Select
Range("A723").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollWorkbookTabs Sheets:=-2
Sheets("List").Select
Range("A1").Select
Sheets("DataNoLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FI").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B10").Select
Range(Selection, Selection.End(xlDown)).Select
Sheets("DataLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FI").Select
Range("A573").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("DataAllLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FI").Select
Range("C581").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A723").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollWorkbookTabs Sheets:=-2
Sheets("DataNoLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Mlrunoff").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("DataLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Mlrunoff").Select
Range("B21").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlDown)).Select
Range("A573").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("DataAllLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Mlrunoff").Select
Range("A583").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A723").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollWorkbookTabs Sheets:=-2
Sheets("DataNoLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("BlueChip").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B8").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A573").Select
Sheets("DataLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("BlueChip").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("DataAllLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("BlueChip").Select
Range("C583").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A723").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G730").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=-2
Sheets("DataNoLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Coalition").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B10").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A573").Select
Sheets("DataLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Coalition").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B584").Select
Range(Selection, Selection.End(xlDown)).Select
Sheets("DataAllLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Coalition").Select
Range("A723").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H728").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=-2
Sheets("DataNoLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CRC").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A8").Select
Range(Selection, Selection.End(xlDown)).Select
Sheets("DataLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CRC").Select
Range("A573").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A583").Select
Range(Selection, Selection.End(xlDown)).Select
Sheets("DataAllLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CRC").Select
Range("A723").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("M730").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=-2
ActiveWindow.SmallScroll Down:=57
Sheets("DataNoLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("New Prog").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B8").Select
Range(Selection, Selection.End(xlDown)).Select
Sheets("DataLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("New Prog").Select
Range("A573").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B579").Select
Range(Selection, Selection.End(xlDown)).Select
Sheets("DataAllLOB").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("New Prog").Select
Range("A723").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("I719").Select
End Sub
Last edited by a moderator: