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
Bookmarks