OzGrid

How to use a Macro to pull every Nth row of data

Requirement:

The user has a workbook with over 20 worksheets with same columns and rows( each has its own name). There is also a master worksheet that is stacked sequentially in the same order. 

The user needs a macro to populate the range R39:AC39 in all the worksheets pulling data from the master worksheet. e.g.:

Sheet 1 R39:AC39 will pull data from the master R39:AC39
Sheet 2 R39:AC39 will pull data from the master R83:AC83
Sheet 3 R39:AC39 will pull data from the master R127:AC127 & SO FORTH.

Solution:

In a Module:

Sub Step44()
  Dim i As Integer, c As Long, m As Worksheet, ws As Worksheet
  
  Set m = Worksheets("Master")
  
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  
  c = -5
  For i = 1 To Worksheets.Count
    Set ws = Worksheets(i)
    If ws.Name = "Master" Then GoTo NextI
    c = c + 44
    m.Range(m.Cells(c, "R"), m.Cells(c, "AC")).Copy ws.Range("R39")
NextI:
  Next i
  
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
  Application.CutCopyMode = False
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Kenneth Hobson.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery