skeeeter56
New Member
- Joined
- Nov 26, 2016
- Messages
- 42
- Office Version
- 2019
- Platform
- 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.
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
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
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
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