Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Split Master sheet to Multiple

    Hi,

    This macro imports the text file, I have Conversion list on Sheet2 and need to split accordingly but i am unable to append the rows to new sheets created by the macro. I need to create a summary sheet. I have uploaded the sample text file and workbook for what i require.

    Code:
    Sub FormatInput()
    On Error Resume Next
    Application.ScreenUpdating = False
    Dim Sht As Worksheet
    Dim ws1 As Worksheet
    Dim LastRow As Integer, EndRow As Integer
    Dim FileToOpen As String
    Dim SheetName As String
    Set ws1 = Worksheets("Conversion")
    Worksheets("MASTER").Select
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
    Application.DisplayAlerts = False
    FileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    s = Split(FileToOpen, "\") 'USED TO DELETE CONNECTION
    t = Split(s(UBound(s)), ".")  'USED TO DELETE CONNECTION
    If FileToOpen = "False" Then Exit Sub
    With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & FileToOpen, Destination:=Range("$A$" & LastRow))
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
    Columns("A:K").Columns.AutoFit
    End With
    For I = LastRow To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        Cells(I, 8).NumberFormat = "0.00"
        For Each Sht In ThisWorkbook.Worksheets
            SheetName = WorksheetFunction.VLookup(Cells(I, 2), ws1.Range("A1:B20"), 2, False)
            If SheetName = Sht.Name Then Sht.Activate: GoTo Skip
        Next Sht
        Worksheets.Add After:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = Worksheets("Master").Cells(I, 2)
    Skip:
        With Worksheets("Master")
        EndRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        
        Cells(EndRow + 1, 1) = .Cells(I, 2)
        For J = 2 To Worksheets("Conversion").Range("F2")
            Cells(EndRow + 1, J) = .Cells(I, J + 2)
            
            
        Next J
        .Activate
        End With
    Next I
    Columns("A:J").AutoFit
    ActiveWorkbook.Connections(t(0)).Delete
    Set Sht = Nothing
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Worksheets("MASTER").Activate
    End Sub
    Attached Files Attached Files

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    ONE SHEET. Why not just import>remove the start-end lines and use
    filter>autofilter for the state. If desired add a column or convert the numbers with a macro to the STATES to filter by.????
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  3. #3
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    This executed on the master sheet after importing the txt file will convert column B to states to filter by. Just HIDE col A on the master sheet

    Option Explicit
    Sub Macro3()
    Dim i As Long
    Dim mf As Range

    For i = 3 To Cells(Rows.Count, _ 1).End(xlUp).Row
    Set mf = Sheets("Conversion").Columns(1).Find(What:=Cells(i , 2), LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If Not mf Is Nothing Then Cells(i, 2).Value = mf.Offset(, 1)
    Next i
    End Sub
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  4. #4
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks for the response Don Guillett.

    I tried the macro, it does not give any output.

    Please have a look.
    Thanks.

  5. #5
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    You did not correct for word wrap. I have attached a file with an improved version of getting the .txt file and changing col B to State. I can't figure out some of your columns but I'm sure you will.

    20150406SampleSAS.xlsm
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  6. #6
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks Don Guillett for helping.

    I need to do sub-total according to state (column A) for column I, with a macro.

    Example:

    Col A .......Col I
    OH...........200000
    OH...........150000
    OH...........550000
    Sub-Total..350000
    CA...........120000
    CA...........150000
    Sub-Total..270000
    LastRow....620000 G Total

    Thanks.

  7. #7
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Total found in col L. See attached

    20150406SampleSAS.xlsm
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  8. #8
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks Don Guillett for providing solution.

    One last thing i tried to add a decimal but result was 200000.00 instead of 2000.00.

    With ThisWorkbook.Sheets("MASTER")
    .Columns(12).Resize(.Rows.Count - 1, 1).Offset(1, 0).NumberFormat = "0.00"
    End With
    Thanks in advance.

  9. #9
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    If your col I had ANY decimals col M would format for .00. Or just add JUST below
    '=====houskeeping
    Columns("L").Style = "Comma"
    Don Guillett
    Excel Developer
    dguillett @gmail.com

Posting Permissions

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