Code that copies values to a sheet after 5 loops needs to move to next page of sheet, then 5 more loops to final page.

skeeeter56

New Member
Joined
Nov 26, 2016
Messages
42
Office Version
  1. 2019
Platform
  1. Windows
I have this code almost completed but having issues.
After the loop has copied 5 values on to destination sheet, I need it to move to next page and continue copying 5 more values and then finally move to next sheet to copy final 4.
VBA Code:
Private Sub cboBusUMS_Click()

        Dim shData As Worksheet, shGroup As Worksheet
      
        Dim arrSh As Variant, arrCe As Variant, arrRn As Variant, arrCl As Variant
      
        Dim i As Long, j As Long, k As Long, lr As Long
      
        Dim outCell As Range, inCell As Range
      
        Application.ScreenUpdating = False
      
        arrSh = Array("Nunawading Bus", "Vermont Bus", "Mitcham Bus", "Blackburn Bus", "Box Hill Bus") 'Names of the 5 destinations Sheets
      
        arrCe = Array(22, 32, 42, 57, 77) 'Rows where arrRn ranges are located,
      
        arrRn = Array("Nuna", "Verm", "Mitch", "Black", "Boxhill") 'The ranges that get copied and each have a number like Nuna1 through to Last Nuna14
      
        arrNm = Array("Name")
      
        arrCo = Array("Code")
      
        arrCl = Array("Clear7", "Clear8", "Clear9", "Clear10", "Clear11") 'This clears the Destinations sheets after Printing is complete
      
        Dim col As Byte, rw As Byte, off As Byte
      
      
            Set shData = ThisWorkbook.Worksheets("Week Commencing")
      
      
            For i = 0 To UBound(arrSh)
      
            rw = 3 'for first 5 items; will be 24 for next 5 and 50 for last 4
      
            col = 3 ' column C for first 5, increase with 2 columns step, reset to column C each 5 items
          
      
            Set shGroup = Sheets(arrSh(i))
          
            k = 1
      
            For j = Columns("D").Column To Columns("Q").Column
      
                If shData.Cells(arrCe(i), j) = False Then
      
              
      
                shGroup.Cells(rw, col).Value = shData.Range(arrNm(0) & k).Value
      
                shGroup.Cells(rw + 1, col).Value = shData.Range(arrCo(0) & k).Value
      
                shGroup.Range(shGroup.Cells(rw + 3, col - 1), shGroup.Cells(rw + 3 + shData.Range(arrRn(i) & k).Cells.Count - 1, col - 1)).Value = shData.Range(arrRn(i) & k).Value
      
        col = col + 2
      
      
      
        'reset row and col at each 5 items
      
            If col = 13 Then
      
            rw = 24
      
            col = 3
      
        End If
      
            If col =Then
      
            rw = 50
      
            col = 3
      
            End If
        Debug.Print col
        Debug.Print rw
        End If
      
        k = k + 1
      
        Next j
      
      
      
            If shGroup.Range("C3") <> "" Then
      
            shGroup.PrintPreview
      
        End If
      
        Next i
      
            For i = 0 To UBound(arrSh)
      
            Set shGroup = Sheets(arrSh(i))
      
            shGroup.Range(arrCl(i)).ClearContents
      
        Next i
      
        Application.CutCopyMode = False
      
        Application.ScreenUpdating = True
      
        End Sub
I have been able to get it to 2nd page just unsure best way to get to 3rd page.
The rw 24 is the row on 2nd page and rw 50 is the 2nd page

VBA Code:
If col = 13 Then
      
            rw = 24
      
            col = 3
      
        End If
      
            If col = 13 Then
      
            rw = 50
      
            col = 3
      
            End If
It copies to the the 2nd page, but unsure how to get it to go to 2nd page as it starts back at 3 and ends up at 13 so does not get ot 2nd If col part
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Forum statistics

Threads
1,215,771
Messages
6,126,799
Members
449,337
Latest member
BBV123

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