+ Reply to Thread
Results 1 to 11 of 11

Transfer important data from a messy rawdata

Hybrid View

  1. #1
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Transfer important data from a messy rawdata

    You can try this:
    Sub a()
        Dim i As Long, ws As Worksheet, s
        Set ws = ActiveSheet
        With Sheets.Add
            For i = 1 To ws.Cells(rows.count, 1).End(xlUp).row
                s = ws.Cells(i, 1).Value
                Select Case True
                    Case s = "", s Like "*CONTROL*", s Like "*MEMO*", s Like "*ACCOUNT*", s Like "*TOT*" 'we don't want these
                        'do nothing
                    Case s Like "*MAC*" 'take these only
                        .Cells(.rows.count, 1).End(xlUp).Offset(1).Resize(, 9).Value = ws.Cells(i, 1).Resize(, 9).Value
                    Case Else
                        'do nothing
                End Select
            Next
        End With
        Set ws = Nothing
    End Sub
    多么想要告诉你 我好喜欢你

  2. #2
    Forum Contributor
    Join Date
    04-22-2013
    Location
    Philippines
    MS-Off Ver
    Office 365, Excel 2016
    Posts
    146

    Re: Transfer important data from a messy rawdata

    Seems I got it, so far I have this running code working:
    Filters MAC and date, will post once I got the correct for the different workbook.

    Sub run()
        Dim i As Long, ws As Worksheet, mc, dt, sysdt
        Set ws = Sheets("INPUT")
        sysdt = WorksheetFunction.Text(ws.Range("K1").Value, "d") & WorksheetFunction.Text(ws.Range("K1").Value, "mm") & WorksheetFunction.Text(ws.Range("K1").Value, "yy")
        
        Sheets("OUTPUT").Select
        
        With Sheets("OUTPUT")
            For i = 1 To ws.Cells(Rows.Count, 1).End(xlUp).Row
                mc = ws.Cells(i, 1).Value
                dt = ws.Cells(i, 3).Value
                Select Case True
                    Case mc Like "*MAC*" And dt Like sysdt 'take these only
                        .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(, 9).Value = ws.Cells(i, 1).Resize(, 9).Value
                    
                    
                    Case Else
                        'do nothing
                End Select
            Next
        End With
        Set ws = Nothing
    End Sub

  3. #3
    Forum Contributor
    Join Date
    04-22-2013
    Location
    Philippines
    MS-Off Ver
    Office 365, Excel 2016
    Posts
    146

    Re: Transfer important data from a messy rawdata

    Final working code from one workbook to another:

    Sub TransferData7()
        Dim i As Long, mc, dt
    	Dim wsOUT, wsIN As Worksheet
    	Dim wbTemplate, wbInput As Workbook
    
        Set wbTemplate = Workbooks.Open(TemplateFile)
        Set wbInput = Workbooks.Open(InputFile)	
    
        Set wsOUT = wbTemplate.Sheets("OUTPUT")
        Set wsIN = wbInput.Sheets("INPUT")
    
        wbInput.Activate
        wsIN .Select
        
        sysdt = WorksheetFunction.Text(wsIN .Range("K1").value, "d") & WorksheetFunction.Text(wsIN .Range("K1").value, "mm") & WorksheetFunction.Text(wsIN .Range("K1").value, "yy")
        
        With wsOUT
            For i = 1 To wsIN .Cells(Rows.Count, 1).End(xlUp).Row
                mc = wsIN .Cells(i, 1).value
                dt = wsIN .Cells(i, 3).value
                Select Case True
                    Case mc Like "*MAC*" And dt Like sysdt 'take these only
                        .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(, 9).value = wsIN .Cells(i, 1).Resize(, 9).value
                    
                    Case Else
                        'do nothing
                End Select
            Next
        End With
        Set wsIN = Nothing
    End Sub

+ 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. [SOLVED] vba to copy data from rawdata and paste into output sheet depends upon code&child
    By johnmacpro in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-01-2016, 06:52 AM
  2. need vba fetch data from pivot table or rawdata depends upon m column value
    By julielara in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-22-2016, 04:35 AM
  3. Help With Formatting Messy Data
    By KimberlyS in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-08-2015, 01:37 PM
  4. [SOLVED] Reorganizing Messy Data
    By Glade1825 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 11-23-2014, 03:01 AM
  5. Cleaning Messy Data for Import
    By Mordred in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-23-2011, 12:14 PM
  6. : help to create critical macro to copy data from rawdata file by 5 condition and pas
    By nirmala_kr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-03-2010, 01:57 PM
  7. messy data
    By Adam in forum Excel General
    Replies: 4
    Last Post: 01-24-2006, 11:55 AM

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