PDA

View Full Version : Copy/Insert union of subranges into existing worksheet



jordansl
05-29-2015, 06:48 AM
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
05-29-2015, 01:55 PM
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:

jordansl
06-01-2015, 05:22 AM
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
06-01-2015, 07:15 AM
If the File No in the Master already exists in the Division sheet, then do nothing??

jordansl
06-01-2015, 07:26 AM
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: