skeeeter56
New Member
- Joined
- Nov 26, 2016
- Messages
- 42
- Office Version
- 2019
- Platform
- Windows
I have code the works exactly as needed if a value is true, but I need to copy it differently if it is false. There are 70 ranges and they are in 5 groups, it loops through each group then the next till it completes, there may zero ranges to copy/print for a group.
These lines of code on each loop pastes into B7 then Prints, then loops for next range to copy till it completes the loop.
This is where I am think Offset could be used, but I tried so many ways to write the code but all gives errors.
What I need it to do is to copy to worksheet differently and instead of printing each loop to do this at end of loop.
I need this to paste the range into B7 then loop back to get next range and paste again, but each loop pastes 2 columns to right of previous.
Once it pastes the 5th time I need it to goto a new page and continue at pasting at B29, then after 5 more loops new page continue from B55. It then prints this worksheet, before moving on to the next group in the other loop
I don't even no if this is possible, I have spent last 2 weeks trying all sorts of ways, what is in the code below is my last attempt.
I hope someone is able to give me some help, or I will just have to give up on it
These lines of code on each loop pastes into B7 then Prints, then loops for next range to copy till it completes the loop.
This is where I am think Offset could be used, but I tried so many ways to write the code but all gives errors.
VBA Code:
If shData.Cells(arrCe(i), j) = False Then
shData.Range(arrRn(i) & k).Copy
shGroup.Range("D6").PasteSpecial Paste:=xlPasteValues
What I need it to do is to copy to worksheet differently and instead of printing each loop to do this at end of loop.
I need this to paste the range into B7 then loop back to get next range and paste again, but each loop pastes 2 columns to right of previous.
Once it pastes the 5th time I need it to goto a new page and continue at pasting at B29, then after 5 more loops new page continue from B55. It then prints this worksheet, before moving on to the next group in the other loop
I don't even no if this is possible, I have spent last 2 weeks trying all sorts of ways, what is in the code below is my last attempt.
VBA Code:
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(23, 33, 43, 58, 78) '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
Set shData = ThisWorkbook.Worksheets("Week Commencing")
For i = 0 To UBound(arrSh)
Set shGroup = Sheets(arrSh(i))
k = 1
For j = Columns("D").Column To Columns("Q").Column
If shData.Cells(arrCe(i), j) = False Then
shData.Range(arrRn(i) & k).Copy
'lr = 6
Worksheets ("Nunawading Bus")
Range("A1").Offset(2, -3).PasteSpecial Paste:=xlPasteValues
'Selection.Offset(3, 1).Range ("A1")
'Can't select unless the sheet is active
'Selection.Offset(3, 1).Range("A1").Select
shGroup.PrintPreview
End If
k = k + 1
Next j
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 hope someone is able to give me some help, or I will just have to give up on it
Last edited by a moderator: