VBA to Populate Stats from one sheet to another

S

surkdidat

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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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 Question
Auto populate data from master worksheet to cooresponding .monthly spreadsheet
Replies
2
Views
382
arun_eagertolearn
A
F
  • Solved
Copy paste from one Excel workbook to another with same sheet names and update dates in range
Replies
8
Views
549
Skiier89
S
H
  • Solved
Reduce size of (or speed up) excel spreadsheet
Replies
5
Views
184
helpcharity
H
C
  • Solved
Newbie: Return values from one sheet to another if not blank
Replies
5
Views
229
cburket
C
S
  • Solved
Transfer of data range (multiple cells) from one sheet to another sheet automatically at the next blank row.
Replies
3
Views
292
Alex Blakenburg
A

Forum statistics

Threads
1,224,931
Messages
6,181,838
Members
453,067
Latest member
mdiz777

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