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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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

willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
280
willow1985
willow1985
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
211
kbk
K
C
  • Question Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
429
myall_blues
myall_blues
N
  • Question Question
Adding to an existing MACRO
Replies
2
Views
103
Northern NY Design
N
D
  • Solved
extract to another workbook and save using desktop path
2
Replies
12
Views
191
jolivanes
jolivanes

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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