VBA to Populate Stats from one sheet to another

S

surkdidat

Well-known Member
Joined
Oct 1, 2011
Messages
582
Office Version
  1. 365
I need to auto populate another sheet, when a button is clicked to do the following please

Main data is on a sheet called "Master"

Data needs to populate on sheet called "Priority"

If there is data on the "Master" sheet in column AC (from Row 3 downwards to row 500) I want the following to happen:

From Master Sheet to Priority Sheet


Column A (Master) to Column A (Priority)

Column - (Master) to Column B (Priority) - Leave Blank

Column B (Master) to Column C (Priority)

Column F (Master) to Column D (Priority)

Column D (Master) to Column E (Priority)

Column E (Master) to Column F (Priority)

Column C (Master) to Column G (Priority)

Column O (Master) to Column H (Priority)

Column P (Master) to Column I (Priority)

Column W (Master) to Column J (Priority)

Column - (Master) to Column K (Priority) - Leave Blank

Column X (Master) to Column L (Priority)

Column - (Master) to Column M (Priority) - Leave Blank

Column Y (Master) to Column N (Priority)

Column - (Master) to Column O (Priority) - Leave Blank

Column Z (Master) to Column P (Priority)

Column - (Master) to Column Q (Priority) - Leave Blank

Column AA (Master) to Column R (Priority)

Column - (Master) to Column S (Priority) - Leave Blank

Column AB (Master) to Column T (Priority)

Column - (Master) to Column U (Priority) - Leave Blank

Column AC (Master) to Column V (Priority)


Hope this makes sense!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
how about
Code: 
Sub MoveData()
   Dim Ws As Worksheet
   Dim Ary As Variant
   Dim i As Long
   
   Set Ws = Sheets("Priority")
   Ary = Array(1, 1, 2, 3, 6, 4, 4, 5, 5, 6, 3, 7, 15, 8, 16, 9, 23, 10, 24, 12, 25, 14, 26, 16, 27, 18, 28, 20, 29, 22)
   With Sheets("Master")
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:AC500").AutoFilter 29, "<>"
      For i = 0 To UBound(Ary) Step 2
        .AutoFilter.Range.Columns(Ary(i)).Copy Ws.Cells(1, Ary(i + 1))
      Next i
      .AutoFilterMode = False
   End With
End Sub
 
Upvote 0
Thank you very much. For my understanding of the code, If I wanted to additionally have a value in Column R as well as Column AC, how would I go about that please? ( I am guessing the reference to AutoFIlter29, relates to COlumn AC, and would like to work out how that effects if you need it on two values please?
Thank you in advance

Fluff said:
how about
Code: 
Sub MoveData()
   Dim Ws As Worksheet
   Dim Ary As Variant
   Dim i As Long
   
   Set Ws = Sheets("Priority")
   Ary = Array(1, 1, 2, 3, 6, 4, 4, 5, 5, 6, 3, 7, 15, 8, 16, 9, 23, 10, 24, 12, 25, 14, 26, 16, 27, 18, 28, 20, 29, 22)
   With Sheets("Master")
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:AC500").AutoFilter 29, "<>"
      For i = 0 To UBound(Ary) Step 2
        .AutoFilter.Range.Columns(Ary(i)).Copy Ws.Cells(1, Ary(i + 1))
      Next i
      .AutoFilterMode = False
   End With
End Sub
Click to expand...
 
Upvote 0
Like
Code: 
Sub MoveData()
   Dim Ws As Worksheet
   Dim Ary As Variant
   Dim i As Long
   
   Set Ws = Sheets("Priority")
   Ary = Array(1, 1, 2, 3, 6, 4, 4, 5, 5, 6, 3, 7, 15, 8, 16, 9, 23, 10, 24, 12, 25, 14, 26, 16, 27, 18, 28, 20, 29, 22)
   With Sheets("Master")
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:AC500").AutoFilter 29, "<>"
     [COLOR=#0000ff] .Range("A1:AC500").AutoFilter 18, "Fluff"[/COLOR]
      For i = 0 To UBound(Ary) Step 2
        .AutoFilter.Range.Columns(Ary(i)).Copy Ws.Cells(1, Ary(i + 1))
      Next i
      .AutoFilterMode = False
   End With
End Sub
 
Upvote 0
ETA: I have "solved that issue - I think!!
surkdidat said:
Thank you very much. For my understanding of the code, If I wanted to additionally have a value in Column R as well as Column AC, how would I go about that please? ( I am guessing the reference to AutoFIlter29, relates to COlumn AC, and would like to work out how that effects if you need it on two values please?
Thank you in advance
Click to expand...
 
Upvote 0
After working out that issue, I tried to repeat the code to populate a second sheet called "PriorityDG"

However, it populates "Priority" from row 3, but wants to populate sheet "PriorityDG" from row2

Where I have repeated the code (and I have added bits to clear and sort the results where under the "PriorityDG" part of my code have I gone wrong to start at row 2 please?

Code: 
Sub Button5_Click()
Sheets("Priority").Select
    Rows("3:397").Select
    Selection.Delete Shift:=xlUp
    Sheets("Master").Select
  Dim Ws As Worksheet
   Dim Ary As Variant
   Dim i As Long
   
   Set Ws = Sheets("Priority")
   Ary = Array(1, 1, 2, 3, 6, 4, 4, 5, 5, 6, 3, 7, 15, 8, 16, 9, 23, 10, 24, 12, 25, 14, 26, 16, 27, 18, 28, 20, 29, 22)
   With Sheets("Master")
      If .AutoFilterMode Then .AutoFilterMode = False
       .Range("A1:AC500").AutoFilter 29, "<>"
      .Range("A1:AC500").AutoFilter 17, "<>DG - WIP"
      For i = 0 To UBound(Ary) Step 2
        .AutoFilter.Range.Columns(Ary(i)).Copy Ws.Cells(1, Ary(i + 1))
      Next i
      .AutoFilterMode = False
   End With
   
   
  Sheets("Priority").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Priority").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Priority").Sort.SortFields.Add Key:=Range("V3:V50" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Priority").Sort
        .SetRange Range("A2:V50")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Sheets("Master").Select
Sheets("Priority").Select
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",R[-1]C+1)"
    Range("B4").Select
    Selection.AutoFill Destination:=Range("B4:B170"), Type:=xlFillDefault
    Range("B4:B170").Select
    
    Sheets("Master").Select


'PriorityDG
  Set Ws = Sheets("PriorityDG")
   Ary = Array(1, 1, 2, 3, 6, 4, 4, 5, 5, 6, 3, 7, 15, 8, 16, 9, 23, 10, 24, 12, 25, 14, 26, 16, 27, 18, 28, 20, 29, 22)
   With Sheets("Master")
      If .AutoFilterMode Then .AutoFilterMode = False
       .Range("A1:AC500").AutoFilter 29, "<>"
      .Range("A1:AC500").AutoFilter 17, "DG - WIP"
      For i = 0 To UBound(Ary) Step 2
        .AutoFilter.Range.Columns(Ary(i)).Copy Ws.Cells(1, Ary(i + 1))
      Next i
      .AutoFilterMode = False
   End With
   
   
  Sheets("PriorityDG").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("PriorityDG").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("PriorityDG").Sort.SortFields.Add Key:=Range("V3:V50" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("PriorityDG").Sort
        .SetRange Range("A2:V50")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Sheets("Master").Select
Sheets("PriorityDG").Select
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",R[-1]C+1)"
    Range("B4").Select
    Selection.AutoFill Destination:=Range("B4:B170"), Type:=xlFillDefault
    Range("B4:B170").Select
    
    Sheets("Master").Select


End Sub
 
Last edited by a moderator:
Upvote 0
This section of code should be adding the data to the PriorityDG sheet from row1.
Code: 
'PriorityDG
  Set Ws = Sheets("PriorityDG")
   Ary = Array(1, 1, 2, 3, 6, 4, 4, 5, 5, 6, 3, 7, 15, 8, 16, 9, 23, 10, 24, 12, 25, 14, 26, 16, 27, 18, 28, 20, 29, 22)
   With Sheets("Master")
      If .AutoFilterMode Then .AutoFilterMode = False
       .Range("A1:AC500").AutoFilter 29, "<>"
      .Range("A1:AC500").AutoFilter 17, "DG - WIP"
      For i = 0 To UBound(Ary) Step 2
        .AutoFilter.Range.Columns(Ary(i)).Copy Ws.Cells([COLOR=#ff0000]1,[/COLOR] Ary(i + 1))
      Next i
      .AutoFilterMode = False
   End With
If you want it to start in row3 change the red1 to 3
 
Upvote 0
Thanks again
Fluff said:
This section of code should be adding the data to the PriorityDG sheet from row1.
Code: 
'PriorityDG
  Set Ws = Sheets("PriorityDG")
   Ary = Array(1, 1, 2, 3, 6, 4, 4, 5, 5, 6, 3, 7, 15, 8, 16, 9, 23, 10, 24, 12, 25, 14, 26, 16, 27, 18, 28, 20, 29, 22)
   With Sheets("Master")
      If .AutoFilterMode Then .AutoFilterMode = False
       .Range("A1:AC500").AutoFilter 29, "<>"
      .Range("A1:AC500").AutoFilter 17, "DG - WIP"
      For i = 0 To UBound(Ary) Step 2
        .AutoFilter.Range.Columns(Ary(i)).Copy Ws.Cells([COLOR=#ff0000]1,[/COLOR] Ary(i + 1))
      Next i
      .AutoFilterMode = False
   End With
If you want it to start in row3 change the red1 to 3
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Distribution of Fund
Replies
1
Views
323
RAMU
R
A
  • Question
Worksheet from a template using a master list but protecting existing worksheets
Replies
0
Views
227
Alfalfa166
A
C
  • Question
Find duplicates when pulling data from other sheets
Replies
3
Views
177
Trebor76
T
L
  • Question
Excel VBA - Return Specific Data from Master Sheet Based on Date Search + Duplicate Certain Items
Replies
1
Views
446
LookingForHelp1
L
GimpyHand
  • Question
Duplicate Copy/Paste in VBA issue
Replies
3
Views
331
Joe4
Joe4

Forum statistics

Threads
1,215,026
Messages
6,122,743
Members
449,094
Latest member
dsharae57

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