Results 1 to 54 of 54

macro/vba to divide data sheet into two sheet

Threaded View

  1. #15
    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 we go again More captures implemented, but when checking for date value in date column, the third cell down is the only value checked in the column. Could be issue if third cell does not have date.

    Sub Twosheets_u()
    Dim sws2, sws3, FileToOpen, sD As String
    Dim x, y, vCol, vCl
    Dim src, trg, src2, trg2, a, Ub, lro, lrr, i, j, lr, n, op, oq, rp, rq As Long
    Dim ws, wso, wsr, wsa, wsb As Worksheet
    Dim OpenWB As Workbook
    Dim EndDate, StartDate As Date
    Dim RngA, Result As Range
    
    Application.ScreenUpdating = False
    
        Set ws = Sheets("studump")
     
        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)
    
        sws2 = InputBox("Please enter the name for the first worksheet?")
            For Each wsa In OpenWB.Worksheets
                If wsa.Name = sws2 Then
                    sws2 = sws2 & Int((900 - 1 + 1) * Rnd + 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 Resume Next
    Pre1:
        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)
        If Err.Number <> 0 Or trg <= src Then
            MsgBox "Don't forget to use commas or use ascending values"
            On Error GoTo -1
            GoTo Pre1
        End If
        On Error Resume Next
    Pre2:
        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)
        If Err.Number <> 0 Or trg2 <= src2 Then
            MsgBox "Don't forget to use commas or use ascending values"
            On Error GoTo -1
            GoTo Pre2
        End If
        On Error Resume Next
    Pre3:
        vCol = InputBox("Please enter the column letters you want transferred, seperated by commas, no spaces." & vbNewLine & "Also Enter date Column")
        vCl = Split(vCol, ",")
        'MsgBox InStr(vCol, ",")
        If Err.Number <> 0 Or InStr(vCol, ",") = 0 Then
            MsgBox "Don't forget to use commas"
            On Error GoTo -1
            GoTo Pre3
        End If
        Ub = UBound(vCl)
        On Error GoTo EH:
    Pre4:
        sD = InputBox("Please enter which of the columns hold the dates." & vbNewLine & "Enter one letter")
        If Not IsDate(ws.Cells(3, sD)) Then 'Possible issue, this line only checks the third cell down
            MsgBox "This column needs to have a date"
            GoTo Pre4
        End If
        Set Result = ws.Columns("A")
        lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
        Set RngA = ws.Range("A1:AV" & lr)
        
        With ws
            .Activate
            For i = lr To 2 Step -1
                If Application.WorksheetFunction.IsText(Cells(i, sD)) _
                Or Cells(i, sD) = 0 Or IsNumeric(Cells(i, sD)) Then
                    Cells(i, sD).EntireRow.Delete
                End If
            Next i
        End With
    
        For n = LBound(vCl) To UBound(vCl)
            With ws
                Set Result = Application.Union(ws.Columns(vCl(n)), Result)
            End With
        Next n
    
        Result.Copy
            wso.Cells(1, 1).PasteSpecial xlPasteValuesAndNumberFormats
        Result.Copy
            wsr.Cells(1, 1).PasteSpecial xlPasteValuesAndNumberFormats
        With wso
            .Activate
            For op = lr To trg Step -1
                If (Cells(op, 1)) <> "" Then Cells(op, 1).EntireRow.Delete
            Next op
            For oq = src To 2 Step -1
                If (Cells(oq, 1)) <> "" Then Cells(oq, 1).EntireRow.Delete
            Next oq
            wso.Columns.AutoFit
        End With
        
        With wsr
            .Activate
            For rp = lr To trg2 Step -1
                If (Cells(rp, 1)) <> "" Then Cells(rp, 1).EntireRow.Delete
            Next rp
            For rq = src2 To 2 Step -1
                If (Cells(rq, 1)) <> "" Then Cells(rq, 1).EntireRow.Delete
            Next rq
            wsr.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 -1
    End Sub
    Last edited by maniacb; 10-16-2020 at 03:52 PM. Reason: remove informational msgbox

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