Copy/Insert union of subranges into existing worksheet

[jordansl]

I need to tailor which data is copied and pasted to the division sheets. An example of the data is attached - the data that goes into the Division sheets is as follows (with columns of Master sheet noted):

File Number (A) | Customer Code (B) | Customer Name (C) | Business Line (F) | Date Invoiced (G) | Controller (H) | Loss (I)

Basically I need to leave out columns D, E, and J as the order of the columns remains the same. The comments column of each division sheet is manually filled in by users.

Here is the full macro (that the incomparable SamT helped me write):

Sub UpdateDivisionsSyr()
      'Update division worksheets with new data (do NOT delete old)
     Dim ShtMaster As Worksheet
     Dim ShtDivision As String
     Dim divIDMaster As Range
     Dim rowIDDivision As Range
     Dim rowID 'As Variant because I don't know it
     Dim Cel As Range
     Dim Found As Range
     Dim LastRow As Long
         
      
     Set ShtMaster = Worksheets("Master")
     With ShtMaster
         LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
         Set divIDMaster = .Range("D5:D" & CStr(LastRow))
     End With
      
      
     For Each Cel In divIDMaster
         Select Case Cel.Value
         Case 10
             ShtDivision = "SYR"
             rowID = Cel.Offset(0, -3)
         Case 20
             ShtDivision = "ROC"
             rowID = Cel.Offset(0, -3)
         Case 30
             ShtDivision = "ALB"
             rowID = Cel.Offset(0, -3)
         Case 40
             ShtDivision = "BUF"
             rowID = Cel.Offset(0, -3)
         Case 50
             ShtDivision = "CLE"
             rowID = Cel.Offset(0, -3)
         Case 60
             ShtDivision = "ORD"
             rowID = Cel.Offset(0, -3)
         Case Else
             MsgBox "Error - Division not found" 'Error handling
         End Select
          
          
         Cel.EntireRow.Copy 'to "PasteSpecial" below
          
         With Sheets(ShtDivision)
             LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
             Set rowIDDivision = .Range("A5:A" & CStr(LastRow))
              
             Set Found = rowIDDivision.Find(rowID)
             If Found Is Nothing Then .Rows(LastRow).Insert Shift:=xlDown
         End With
     Next Cel
      
 End Sub

I'm trying to set up a Union to pull the range I need out of the row that Cel exists in, here is what I have to replace Cel.EntireRow.Copy:

 Dim PasteData as Range, rng1 As Range, rng2 As Range

 Set rng1 = .Range(Cel.Offset(0,-3),Cel.Offset(0,-1))  'This selects columns A through C of the current row
 Set rng2 = .Range(Cel.Offset(0,2),Cel.Offset(0,5))   'This selects columns F through I of the current row

 Set PasteData = Union(rng1,rng2)  'This selects both ranges and puts them together

Application.PasteData.Copy

When I ran this the first time, it looked like it worked (ie no errors) but going through the worksheets I saw that only the first line of data for each division sheet had been copied, then extra blank lines appeared below it. It looked like the macro knew how many rows needed to be inserted, but the data didn't go in at all. Since then I've tried to tweak it to work but I only get errors and the macro will not run.

I think "If Found Is Nothing Then .Rows(LastRow).Insert Shift:=xlDown" is also giving me some trouble but I haven't been able to pinpoint the source yet.

Thanks for your help!

[SamT]

Looking at your logic, I deduce that there is a bottom row of data that must be kept.

The attachment "ForSam" does not follow the requirements stated above. I suggest that you keep the Master sheet as is, but redo the division sheets old data to match your needs. Then give us the Master Column to Division Column correlation.

I got the code to work per stated requirements, but it didn't fit the layout of the division sheets.

[jordansl]

Hi again!

So you're absolutely right about the bottom row of data - I inserted the new data one row above the bottom so that all formatting would be maintained - if there's a better way to do this please let me know!

I think perhaps I'm confusing you with my description - the attached sheet is the correct format. I was trying to say that I need columns A, B C, F, G H and I from the Master sheet to be copied and then pasted into the first seven columns of the division sheets. Nothing will be pasted into the comments column as this is updated manually by users.

Here is the exact mapping (with Column headings and letters):

Master Sheet ====>Division Sheet
File No (A)====>File No (A)

Customer Code (B)====>Customer Code (B)

Customer Name (C)====>Customer Name (C)

Business Line (F)====>Business Line (D)

Date Invoiced (G)====>Date Invoiced (E)

Controller (H)====>Controller (F)

Loss (I)====>Loss (G)

[SamT]

If the File No in the Master already exists in the Division sheet, then do nothing??

[jordansl]

If the File No in the Master already exists in the Division sheet, then do nothing??

Yeah that's correct. This is only to update the Division sheets with new data - the users will clear out old data as they see fit. Sorry I should have clarified