Results 1 to 9 of 9

extraction

Threaded View

rowena extraction 10-05-2006, 10:31 PM
Myles What exactly do you want to... 10-06-2006, 01:13 AM
rowena Please Help!! 10-06-2006, 04:34 AM
antoka05 Try with the worksheet in... 10-06-2006, 05:14 AM
rowena thanks for help!! 10-08-2006, 10:37 PM
  1. #1
    Registered User
    Join Date
    10-05-2006
    Posts
    6

    extraction

    i need to etract data from SourceSheet to DestinationSheet, duplication allowed. The code seem like no error, but there is no output on destination sheet.

    Below is the code:

    Sub extract()
    
    'on Local Error Go To arr:
        If Sheets.Count < 2 Then
            MsgBox "Please add more sheets!"
            Exit Sub
        End If
        
        Sheets(2).Range("C:C").NumberFormat = "@"   
        Sheets(2).Cells.EntireColumn.NumberFormat = "general"
        
        Sheets(2).Cells.Delete
        Sheets(1).Name = "sourceSheet"
        Sheets(2).Name = "destinationSheet"
        Sheets(2).Range("A2").Value = "No"
        Sheets(2).Range("B2").Value = "Code
        Sheets(2).Range("C2").Value = "SupName"
    
        Sheets(2).Rows("1:1").Font.Bold = True
        Sheets(2).Rows("2:2").Font.Bold = True
       
        Dim i As Long
        Dim j As Long
        Dim k As Long
        Dim l As Long
        Dim lstRow As Long
        Dim lstCol As Long
        Dim so As Long
        
        
      
        i = 1     'sourceSheet starting row no
        j = 3     'destinationSheet starting row no
        k = 0     'destinationSheet column count  
        l = 2     'count how many code  in sheets 1
        lstRow = Sheets(1).Cells(16384, 1).End(xlUp).Row
        lstCol = Sheets(1).Range("A1").End(xlToRight).Column
        
          'calculate how many code column available in sheet 1
        Do Until l > lstCol
            If Not InStr(Sheets(1).Cells(1, l).Value, "AVL") = 0 Then
                 k = k + 1
             End If
        
             l = l + 1
        Loop
        
    
        'get data from sheet 1
        Do Until i > lstRow
            so = 1      'SourceSheet column counter
            l = 2       'code Col index in sheet1
                    
    Do Until so > k
            
                If Not Sheets(1).Cells(i, l).Value = "" Then
            
                    Sheets(2).Range("A" & j).Value = Sheets(1).Range("A" & i).Value
                    Sheets(2).Range("B" & j).Value = Sheets(1).Cells(i, l).Value
                    Sheets(2).Range("C" & j).Value = Sheets(1).Cells(i, l + 1).Value
                    j = j + 1    'move to next row of sheet2
                End If
                
                'l = l + 2
                'so = so + 1
                
           Loop
            
            i = i + 1           ' move to next row of sheet1
        
        Loop
    
        
        Sheets(2).Cells.EntireColumn.AutoFit
        
        Exit Sub
        
    arr:
        MsgBox "Format Not Supported Row: " & i
        
    End Sub
    Last edited by VBA Noob; 11-02-2008 at 05:20 PM.

Thread Information

Users Browsing this Thread

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

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