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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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

D
  • Solved
Insert formula in last column macro
2
Replies
13
Views
215
MARK858
MARK858
S
  • Question Question
Dynamic Address by VBA
Replies
8
Views
212
MikeVol
MikeVol
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
400
willow1985
willow1985
R
  • Question Question
I keep getting the error 91 but i cant figure out what is wrong here.
Replies
2
Views
60
mumps
mumps
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
230
kbk
K

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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