Is Offset the best way to copy a range to another worksheet and each time it loops it moves 2 columns to the right

skeeeter56

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


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:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

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