I have written a code but it's not working I want to copy a range F2:F24 from sheet1 and paste it to Sheet2 in incremental column-wise (Column_count+1), only if cell F2 value in sheet1 should not be equal to the last column of the first row in sheet2 If it matches then popup msgbox as "check_the _cell" Here is my code
Sub copycolumns()
Dim TargetSheet As Object
Set TargetSheet = Sheets("sheet2")
Dim TargetColumn As Integer
Dim LastC As Long
TargetColumn = TargetSheet.Range("F1").CurrentRegion.Columns.Count + 1
LastC = TargetSheet.Cells(1, TargetSheet.Columns.Count).End(xlToLeft).Column
If LastC = Sheets("sheet1").Cells(2, 6).Value Then
MsgBox "check the cell"
ElseIf TargetSheet.Range("F1") = "" Then
TargetColumn = 6
End If
Sheets("sheet1").Range("F2:F24").Copy
TargetSheet.Activate
TargetSheet.Cells(1, TargetColumn).Select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
Update_1: Before copying and pasting the RangeF2:F24 from Sheet1, it should compare the value of F2 (refer Image1) of Sheet1 and the last column of the first cell from sheet2 (Refer Image2, it is J1). If its value is the same then msgpop as error. if its value is different then copy F2:F24 and paste in last column of first row in sheet2
Sheets("sheet1").Cells(2, 6).Value
be dynamic row? Else you always check same cell. – Kin Siang