Copy/Insert union of subranges into existing worksheet

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):

Code:

---

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:

Code:

---

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!

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

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. :banghead::banghead::banghead:

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)

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

Quote:

---

Originally Posted by SamT

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 :doh: