+ Reply to Thread
Results 1 to 54 of 54

macro/vba to divide data sheet into two sheet

Hybrid View

  1. #1
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: macro/vba to divide data sheet into two sheet

    Here is the latest code iteration. It should work to resolve the renaming if sheet exist and removing the non relevant dates. I say should because it works when I manually step through the code, but gives issues when I run it straight. Test it out

    Sub Twosheets()
    Dim sws2, sws3, FileToOpen As String
    Dim x, y, vCol, vCl
    Dim src, trg, src2, trg2, a, Ub, lro, lrr, i, j As Long
    Dim ws, wso, wsr, wsa, wsb As Worksheet
    Dim OpenWB As Workbook
    Dim EndDate, StartDate As Date
    
    Application.ScreenUpdating = False
        Set ws = Sheet1
        On Error GoTo EH:
        FileToOpen = Application.GetOpenFilename(Title:="Select File to Open where sheets will be added", _
                     FileFilter:="Excel Files (*.xls*),*xls*")
        If FileToOpen = "False" Then Exit Sub
        Set OpenWB = Workbooks.Open(Filename:=FileToOpen, ReadOnly:=False)
        On Error GoTo -1
        'On Error Resume Next
        sws2 = InputBox("Please enter the name for the first worksheet?")
            For Each wsa In OpenWB.Worksheets
                If wsa.Name = sws2 Then
                    sws2 = sws2 & 1
                Else
                    sws2 = sws2
                End If
            Next wsa
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = sws2
        Set wso = OpenWB.Sheets(sws2)
        sws3 = InputBox("Please enter the name for the 2nd worksheet?")
            For Each wsb In OpenWB.Worksheets
                If wsb.Name = sws3 Then
                    sws3 = sws3 & 1
                Else
                    sws3 = sws3
                End If
            Next wsb
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = sws3
        Set wsr = OpenWB.Sheets(sws3)
        On Error GoTo -1
        x = Split(InputBox("type from_what_row_number_in_sheet1, to_what_row_number_in_sheet1. Example : 2,200 "), ",")
        src = x(0)
        trg = x(1)
    
        y = Split(InputBox("type from_what_row_number_in_sheet2, to_what_row_number_in_sheet2. Example : 200,500 "), ",")
        src2 = y(0)
        trg2 = y(1)
    
        vCol = InputBox("Please enter the column letters you want transferred, seperated by commas, no spaces." & vbNewLine & "Enter atleast a,b")
        vCl = Split(vCol, ",")
        Ub = UBound(vCl)
    
        StartDate = "30 Dec 2005"
        EndDate = "1 jan 2017"
        With wso
            ws.Range(vCl(0) & "1:" & vCl(Ub) & "1").Copy
            wso.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
            ws.Range(vCl(0) & src & ":" & vCl(Ub) & trg).Copy
            wso.Range("A2").PasteSpecial xlPasteValuesAndNumberFormats
            lro = wso.Cells(Rows.Count, "A").End(xlUp).Row
            For i = lro To 2 Step -1
                If Application.WorksheetFunction.IsText(Cells(i, "B")) _
                Or Cells(i, "B") = 0 Or Cells(i, "B") >= EndDate _
                Or Cells(i, "B") <= StartDate Then
                    Cells(i, "B").EntireRow.Delete
                End If
            
            Next i
            Columns.AutoFit
        End With
        With wsr
            ws.Range(vCl(0) & "1:" & vCl(Ub) & "1").Copy
            wsr.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
            ws.Range(vCl(0) & src2 & ":" & vCl(Ub) & trg2).Copy
            wsr.Range("A2").PasteSpecial xlPasteValuesAndNumberFormats
            lrr = wsr.Cells(Rows.Count, "A").End(xlUp).Row
            For j = lrr To 2 Step -1
                If Application.WorksheetFunction.IsText(Cells(j, "B")) _
                Or Cells(j, "B") = 0 Or Cells(j, "B") >= EndDate _
                Or Cells(i, "B") <= StartDate Then
                    Cells(j, "B").EntireRow.Delete
                End If
            Next j
            Columns.AutoFit
        End With
        
        With Application
        .CutCopyMode = False
        .ScreenUpdating = True
        End With
    Exit Sub
    EH:
        MsgBox "This is an error  " & Err.Description
        With Application
        .CutCopyMode = False
        .ScreenUpdating = True
        End With
        On Error GoTo 0
    End Sub

  2. #2
    Registered User
    Join Date
    10-11-2020
    Location
    India
    MS-Off Ver
    2013
    Posts
    36

    Re: macro/vba to divide data sheet into two sheet

    Following issues appeared
    1)The data is not getting copied into second sheet (issue reappeared)
    2)blank cells and texts are not getting removed from date column. (the record(whole row) having blank cell or only text in date column should get deleted)

    Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to fill the data into Summary sheet basis of different scenarios from Data sheet
    By Manish_Gupta in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-19-2016, 11:48 AM
  2. Macro to divide sheet
    By excel1983 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2014, 12:19 PM
  3. [SOLVED] Macro to copy data from a master sheet to separate sheet as per date using a macro
    By tmaster81 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-23-2014, 08:05 AM
  4. Replies: 2
    Last Post: 05-01-2013, 12:26 PM
  5. [SOLVED] Macro to Copy Data from one Sheet A to Sheet B based on value in cell on sheet A
    By scass in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-11-2012, 07:21 PM
  6. Macro/Module running as background process, copy data from sheet to sheet
    By ctor in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-05-2009, 05:48 AM
  7. Need macro to copy data from sheet 1 to multiple pages on sheet 2 in correct cells
    By iturnrocks in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-16-2009, 03:33 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1