Move rows from one sheet in a workbook to another workbook

Shathrugan

New Member
Joined
Sep 6, 2018
Messages
1
Hi all,

I have a workbook1 with a single sheet containing multiple rows of data.
I need to create another workbook2 with each sheet consisting one row from workbook1.
To be more clear one row from workbook1 should be contained In one single sheet in workbook2.
If suppose workbook1 has 20 rows of data ,then workbook 2 should have 20 sheets with each row data respectively.Please help in achieving this using macro
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,619
This macro assumes the sheet in workbook1 containing your data is named "Sheet1" and the data is in column A starting at row 1. The macro can be modified to suit your needs.
Code:
Sub CreateSheet()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet
    Set srcWS = ThisWorkbook.Sheets("Sheet1")
    Dim LastRow As Long, rng As Range
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Workbooks.Add
    For Each rng In srcWS.Range("A1:A" & LastRow)
        Worksheets.Add
        ActiveSheet.Move After:=Worksheets(Worksheets.Count)
        srcWS.Rows(rng.Row).EntireRow.Copy Cells(1, 1)
    Next rng
    Application.ScreenUpdating = True
 End Sub
 

Forum statistics

Threads
1,086,123
Messages
5,387,978
Members
402,092
Latest member
S_S

Some videos you may like

This Week's Hot Topics

Top