Insert and copy dynamic rows between workbook A and workbook B

lojamescc

New Member
Joined
Aug 1, 2024
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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
 

Attachments

  • Workbook A.PNG
    Workbook A.PNG
    23.8 KB · Views: 9
  • Workbook B.PNG
    Workbook B.PNG
    23.6 KB · Views: 9

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
May be this macro:
Code:
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...
 
Upvote 0

Forum statistics

Threads
1,223,374
Messages
6,171,713
Members
452,418
Latest member
kennettz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top