In a workbook of mine there are 2 worksheets (Sheet1 & Sheet2) and Sheet1 has some data like (first image)
I'm trying to copy the data from columns SERIAL NO., HS CODE and PALLET MMT to Sheet2's columns PROD. ID, HS CODE & NET WT. respectively. Now the first two copies are pretty straight forward but the problem I'm having is generating NET WT. (it is the product of two numbers inside the brackets & divided by 1000)
Default Sheet2 looks like: (to the right of the first image)
Result Sheet2 data should look like: (last image)
Note: I want to get the range to copy to & the range to paste dynamically and not hard code the ranges.
I've done:
Code:
Sub Macro1()
'
' Macro1 Macro
'
'
Cells.Find(What:="SERIAL", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet2").Select
Cells.Find(What:="PROD", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("Sheet1").Select
Range("A1").Select
Cells.Find(What:="CODE", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet2").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
' @@@@@@@@@@ NET WT. ?????? @@@@@@@@@@@@@
End Sub
I've also made a function to calculate NET WT. & but struggling to figure out how to use it in my code without making it too complicated
Code:
Function netWT(CellRef As String)
Dim i As Long, Result As String, ch As String
For i = 1 To Len(CellRef)
ch = Mid(CellRef, i, 1)
Result = Result & IIf(ch Like "[0-9]", ch, " ")
Next i
Result = Application.Trim(Result)
netWT = (Split(Result, " ")(1) * Split(Result, " ")(2)) / 1000
End Function
Help please. Different approach than mine are also welcome...It just has to do the job efficiently.