VBA to Populate Stats from one sheet to another

surkdidat

Well-known Member
Joined
Oct 1, 2011
Messages
594
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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

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
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!!
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
 
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
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

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

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