'Add or remove rows in destination array to exactly fit source data
I'm using this subroutine to copy-paste data between two opened workbooks ...
Sub Import_Recap()
Dim wb As Workbook, x As String
For Each wb In Workbooks
If wb.Name <> ThisWorkbook.Name Then x = wb.Name
Next wb
If x = "" Then
MsgBox "Open the source workbook first !!!", vbInformation
Exit Sub
End If
Dim wsSource As Worksheet, wsDest As Worksheet
Set wsSource = Workbooks(x).Sheets(1)
Set wsDest = ThisWorkbook.Sheets(3)
wsDest.Activate
wsDest.ShowAllData
wsSource.Rows("1:17").Delete Shift:=xlUp
wsSource.Range("B:B,C:C,G:G,J:J").Delete Shift:=xlToLeft
y = wsSource.Range(wsSource.Range("A1"), wsSource.Range("A1").End(xlDown)).Rows.Count
z = wsDest.Range(Range("A10"), Range("A10").End(xlDown)).Rows.Count
j = y - z
wsDest.Range(Range("A10"), Range("A10").End(xlDown).End(xlToRight)).ClearContents
If j > 0 Then
For i = 1 To j
wsDest.Rows("15:15").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Next i
ElseIf j < 0 Then
For i = 1 To j * -1
wsDest.Rows("15:15").Delete Shift:=xlUp
Next i
End If
returnvalue = MsgBox("Do you want to import data from: " & x & ".", vbYesNo)
If returnvalue = 6 Then ''YES button clicked
wsSource.Range("A1").CurrentRegion.Copy wsDest.Range("A10")
ElseIf returnvalue = 7 Then ''NO button clicked
Exit Sub
End If
End Sub
The two arrays have the same columns, only the number of rows in the source workbook is different every time. There is also other data below the currently used range in destination workbook and a specific format, which I want to keep. I was thinking of comparing the used rows count in source and destination current regions and then using Do while loop to insert or delete rows somewhere in the middle of destination until equals. Is there a nicer way of doing something like this?
Edit: I've updated the code above with a solution. I've used two for loops instead of do while/until ... still feels kinda weird doing it that way, but at least it's working.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
