Insert and copy dynamic rows between workbook A and workbook B

[lojamescc]

Dear Excel Experts,

I have 2 workbooks: Workbook A and Workbook B. Workbook A contains 4 worksheets (A1,A2,A3,A4). These worksheets contain different number of rows. The number of rows from these worksheets MUST be copied to workbook B with corresponding the same worksheet names A1,A2,A3,A4.
How can I write a VBA to insert these dynamic rows for each worksheet mentioned above and copy the corresponding row data from workbook A (if any) to workbook B with minimum hardcoding. All valid data starts at A2 on each worksheet and ends at N column. Insertion on workbook B always at cell A2 of workbook B. If there is NO record on the row, i need to skip it. I know the workflow programming logic but can't fully utilize the syntax of VBA to fully exploit it.

Thank you in advance.

JL

 

[Anthony47]

May be this macro:

Sub FromAtoB()
Dim WbFrom As Workbook, WbTo As Workbook
Dim cWs As Worksheet, cLR As Long
'
Set WbFrom = ActiveWorkbook     'The source file to copy from
Set WbTo = ThisWorkbook         'The file containing the macro
'
WbFrom.Activate                 '*** See text
'
For Each cWs In WbFrom.Worksheets
    'Last used row in sheet
    On Error Resume Next
    cLR = 0
    cLR = cWs.Range("A:N").Find(What:="*", After:=cWs.Range("A1"), _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious).Row
    On Error GoTo 0
    Debug.Print cWs.Name, Timer, cLR
    If cLR > 1 Then             'Copy:
        WbTo.Sheets(cWs.Name).Range("A2").Resize(Rows.Count - 2, 20).Clear
        cWs.Range("A2").Resize(cLR - 2, 14).Copy _
          Destination:=WbTo.Sheets(cWs.Name).Range("A2")
    End If
Next cWs
Application.CutCopyMode = False
End Sub

The code assumes that the macro is stored within the destination workbook and that the "source" workbook is the active one; then it will scan the worksheets in the source file and check how many rows af data are in that worksheet; if there are data after line 1 then the "same" worksheet in the destination file is cleared (from line 2 on) and the source data are copied there (the previous data will be left in the worksheet if the source data is empty!)

The line marked *** See text is obviously useless if the source file is the active one; it is there as a reminder in case that you modify the source file, for example if you ask the user to select the file to import data and then open it.

The code could have been shorter if you had described the layout of your data

Try...