Rakesh Kamani
New Member
- Joined
- Feb 25, 2020
- Messages
- 33
- Office Version
- 2013
- Platform
- Windows
Hi All,
I am new to VBA, I already have a code, we are going to add some logic in this code. Below I am going to explain the requirement. Please help with this.
I would like to copy Column-K to Column-V and paste it same worksheet from Column-W to AH and so on, which created new "FG" sheet in the same workbook based on another worksheet("07_Safety_Sheet") range(A5:A12). if "A5" value update is manually it will automatically copy Column-K to Column-V and paste into same worksheet from Column-W to AH. Please correction and add logic to the code. otherwise please provide module to copy Column-K to V and let me know where we going to call in the code.
Thank you for your help in advance.
I am new to VBA, I already have a code, we are going to add some logic in this code. Below I am going to explain the requirement. Please help with this.
I would like to copy Column-K to Column-V and paste it same worksheet from Column-W to AH and so on, which created new "FG" sheet in the same workbook based on another worksheet("07_Safety_Sheet") range(A5:A12). if "A5" value update is manually it will automatically copy Column-K to Column-V and paste into same worksheet from Column-W to AH. Please correction and add logic to the code. otherwise please provide module to copy Column-K to V and let me know where we going to call in the code.
Thank you for your help in advance.
VBA Code:
Private Sub CommandButton1_Click()
'declaring veriables
Dim wB As Workbook
Dim wS As Worksheet
Dim wT As Worksheet
Dim wD As Worksheet
Dim wDB As Worksheet
Dim wSM As Worksheet
Dim strF As String
Dim strT As String
Dim strTemp As String
Dim intI As Integer
Dim intJ As Integer
Dim intC As Integer
Dim intS As Integer
Dim intR As Integer
Dim intX As Integer
Dim intY As Integer
Dim rngDB As Range
Dim rngCell As Range
Dim rngSM As Range
Dim rngCellSM As Range
Dim strFormulea As String
On Error Resume Next
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
'Assigning sheet, that has data available
Set wS = ThisWorkbook.Worksheets("Source_Sheet")
Set wT = ThisWorkbook.Worksheets("template_Sheet_xxx")
Set wDB = ThisWorkbook.Worksheets("04_Component_Sheet")
Set wSM = ThisWorkbook.Worksheets("07_Safety_Sheet")
Set rngDB = wDB.Range("B85:B188")
Set rngSM = wSM.Range("A5:A14")
strF = "FG"
intS = 1
intI = 0
intJ = 0
Do
If wS.Range("A7").Offset(intI, 0).Value = "" Or wS.Range("A7").Offset(intI, 0).Value = "EoF" Then Exit Do
strT = wS.Range("A7").Offset(intI, 0).Value
If VBA.InStr(1, strT, strF) Then
DelExistSheet ThisWorkbook, strT
'To add new sheet, if there is no exiting sheet for the given Functional Group
'Set wD = ThisWorkbook.Worksheets.Add(after:=wS)
Set wD = Worksheets.Add(after:=Worksheets(Worksheets.Count))
intJ = 1
Do
If VBA.InStr(1, wS.Range("A7").Offset(intI + intJ, 0).Value, strF) Or VBA.InStr(1, wS.Range("A7").Offset(intI + intJ, 0).Value, "EoF") Then Exit Do
' intC = 0
intJ = intJ + 1
Loop
intC = intJ - 1
wD.Name = strT
'To add header row to each added sheet
wT.Range("A1:V6").Copy wD.Range("A1")
'copying rows from Source_Sheet, one by one add pasting to next available line on Created FG sheet
wS.Range("A7").Offset(intI + 1, 0).Resize(intC, 1).Copy wD.Range("B7")
wS.Range("I7").Offset(intI + 1, 0).Resize(intC, 1).Copy wD.Range("A7")
wS.Range("B7").Offset(intI + 1, 0).Resize(intC, 1).Copy wD.Range("C7")
wS.Range("C7").Offset(intI + 1, 0).Resize(intC, 1).Copy wD.Range("D7")
'wD.Range("F7").Resize(intC).Value = wS.Range("L7").Offset(intI + 1, 0).Resize(intC, 1).Value
'wD.Range("G7").Resize(intC).Value = wS.Range("V7").Offset(intI + 1, 0).Resize(intC, 1).Value
'strTemp = VBA.Left(strFormulea, VBA.InStr(1, strFormulea, ",") - 1)
'strTemp = VBA.Replace(strTemp, "VLOOKUP(", "")
'strFormulea = VBA.Replace(strFormulea, strTemp, "RC[-5]")
'wD.Range("F7").Resize(intC, 1).FormulaR1C1 = strFormulea
'strFormulea = wS.Range("L7").Offset(intI + 1, 0).FormulaR1C1
'strTemp = VBA.Replace(strTemp, "VLOOKUP(", "")
'strFormulea = VBA.Replace(strFormulea, strTemp, "RC[-6]")
'wD.Range("G7").Resize(intC, 1).FormulaR1C1 = strFormulea
intX = 0
Do
If wD.Range("A7").Offset(intX, 0).Value = "" Then Exit Do
For Each rngCell In rngDB
If VBA.InStr(1, rngCell.Value, wD.Range("A7").Offset(intX, 0).Value) Then
intR = rngCell.Row
intY = rngCell.MergeArea.Cells.Count
Exit For
End If
Next rngCell
wD.Range("A7").Offset(intX + 1, 0).Resize(intY - 1, 26).Insert Shift:=xlDown
wD.Range("H7").Offset(intX, 0).Resize(intY, 1).Value = wDB.Cells(intR, 4).Resize(intY, 1).Value
wD.Range("I7").Offset(intX, 0).Resize(intY, 1).Value = wDB.Cells(intR, 5).Resize(intY, 1).Value
strFormulea = "= VLOOKUP(RC[-5],'Source_Sheet'!R5C9:R83C22,4,0)"
wD.Range("F7").Offset(intX, 0).Resize(intY, 1).FormulaR1C1 = strFormulea
strFormulea = "= VLOOKUP(RC[-6],'Source_Sheet'!R5C9:R83C22,14,0)"
wD.Range("G7").Offset(intX, 0).Resize(intY, 1).FormulaR1C1 = strFormulea
strFormulea = "=R" & 7 + intX & "C7*RC9*RC[-3]"
wD.Range("O7").Offset(intX, 0).Resize(intY, 3).FormulaR1C1 = strFormulea
strFormulea = "=IF(RC[-1]<>" & """""" & ",VLOOKUP(RC[-1],'07_Safety_Sheet'!R18C2:R40C3,2,FALSE),0)"
wD.Range("S7").Offset(intX, 0).Resize(intY, 1).FormulaR1C1 = strFormulea
strFormulea = "=RC[-5]*(100%-RC[-1])"
wD.Range("T7").Offset(intX, 0).Resize(intY, 1).FormulaR1C1 = strFormulea
strFormulea = "=RC[-5]*(100%-RC[-2])"
wD.Range("U7").Offset(intX, 0).Resize(intY, 1).FormulaR1C1 = strFormulea
intX = intX + intY
Loop
intX = 0
Do
If wD.Range("H7").Offset(intX, 0).Value = "" Then Exit Do
intY = 1
Do
If wD.Cells(7 + intX + intY, 1).Value <> "" Or wD.Range("H7").Offset(intX + intY, 0).Value = "" Then Exit Do
intY = intY + 1
Loop
'Merge Column cells from A to G
wD.Cells(7 + intX, 1).Resize(intY, 1).Merge
wD.Cells(7 + intX, 1).Resize(intY, 1).HorizontalAlignment = xlCenter
wD.Cells(7 + intX, 1).Resize(intY, 1).VerticalAlignment = xlCenter
wD.Cells(7 + intX, 2).Resize(intY, 1).Merge
wD.Cells(7 + intX, 2).Resize(intY, 1).HorizontalAlignment = xlCenter
wD.Cells(7 + intX, 2).Resize(intY, 1).VerticalAlignment = xlCenter
wD.Cells(7 + intX, 3).Resize(intY, 1).Merge
wD.Cells(7 + intX, 3).Resize(intY, 1).HorizontalAlignment = xlCenter
wD.Cells(7 + intX, 3).Resize(intY, 1).VerticalAlignment = xlCenter
wD.Cells(7 + intX, 4).Resize(intY, 1).Merge
wD.Cells(7 + intX, 4).Resize(intY, 1).HorizontalAlignment = xlCenter
wD.Cells(7 + intX, 4).Resize(intY, 1).VerticalAlignment = xlCenter
wD.Cells(7 + intX, 5).Resize(intY, 1).Merge
wD.Cells(7 + intX, 5).Resize(intY, 1).HorizontalAlignment = xlCenter
wD.Cells(7 + intX, 5).Resize(intY, 1).VerticalAlignment = xlCenter
wD.Cells(7 + intX, 6).Resize(intY, 1).Merge
wD.Cells(7 + intX, 6).Resize(intY, 1).HorizontalAlignment = xlCenter
wD.Cells(7 + intX, 6).Resize(intY, 1).VerticalAlignment = xlCenter
wD.Cells(7 + intX, 7).Resize(intY, 1).Merge
wD.Cells(7 + intX, 7).Resize(intY, 1).HorizontalAlignment = xlCenter
wD.Cells(7 + intX, 7).Resize(intY, 1).VerticalAlignment = xlCenter
intX = intX + intY '+ 1
Loop
End If
intI = intI + intJ
Loop
'Application.ScreenUpdating = True
With Application
.ScreenUpdating = True
.DisplayAlerts = False
End With
MsgBox "created Functional Group from Source_Sheet"
End Sub
Sub DelExistSheet(wB As Workbook, SheetName As String)
On Error Resume Next
Dim wS As Worksheet
Set wS = wB.Worksheets(SheetName)
wS.Delete
End Sub