Macro/VBA trouble

A

Aleks88

New Member
Joined
Oct 16, 2018
Messages
2
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?

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:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
I looked at your question earlier and was hoping someone else would have a answer because I could not understand your request.

First you said:
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"

Please define Drop Down

Do you mean a Data Validation list
Or A Activex Combobox

And if Data Validation List then in what cell is this Drop down.

I would never use the term drop down

Use Data Validation list or Combobox

And when you choose a value please explain in words what you want.

You should never need this much code to do a Task
​
 
Upvote 0
Hi! Yes, when I say drop down I mean data validation. So I go to my tab called “List” and select a value in cell A1, for example CYB, I then have indirect formulas run that populate data in the other 3 tabs. I want the macro to pick up all 3 tabs and paste them in one sheet vertically. I then want the macro to run automatically for all the other values in data validation and paste them into different tabs in the workbook.
Is there a way to add attachment to this thread?
 
Upvote 0
I see your new to the forum.

Your still providing very few specific details.
When you say:

I want the macro to pick up all 3 tabs and paste them in one sheet vertically.

Well a Tab is a sheet. And you do not give the sheet names

And its impossible to pickup three sheets and paste them all into one sheet.
And you do not give us the name of the sheet you want them pasted into.

We always need specific details.​
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
165
shknbk2
S
P
  • Solved
Counter in For Each loop
Replies
2
Views
195
Possum17
P
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
307
Azamamir
A
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
172
pennbowl
P
P
  • Solved
Recorded Macro Ammendments
Replies
3
Views
342
Joe4
Joe4

Forum statistics

Threads
1,214,851
Messages
6,121,931
Members
449,056
Latest member
denissimo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top