How to write VBA to copy and paste special from one column to another column with 1 Criteria

[kitsa]

Hi,
I'm struggling to write a VBA for criteria "S" or "V" in Column ("C:C"). If value is True? Move Column ("H:H") to Column ("I:I") as paste special. From "Sheet1" or "ESS Inv Data Entry" sheet.
Can someone help me write this?

 

[DanteAmor]

Run this macro on the sheet you want it to work on, that is, if you want it to work on the Sheet1 sheet, select the Sheet1 sheet and run the macro.

Sub moveValue()
  Dim i As Long
  Application.ScreenUpdating = False
  For i = 2 To Range("C" & Rows.Count).End(3).Row
    If Range("C" & i).Value = "S" Or Range("C" & i).Value = "V" Then Range("H" & i).Cut Range("I" & i)
  Next
  Application.ScreenUpdating = True
End Sub

 

[kitsa]

Run this macro on the sheet you want it to work on, that is, if you want it to work on the Sheet1 sheet, select the Sheet1 sheet and run the macro.

Sub moveValue()
  Dim i As Long
  Application.ScreenUpdating = False
  For i = 2 To Range("C" & Rows.Count).End(3).Row
    If Range("C" & i).Value = "S" Or Range("C" & i).Value = "V" Then Range("H" & i).Cut Range("I" & i)
  Next
  Application.ScreenUpdating = True
End Sub

Hi Dante,
The macro:
* Removed Values in column "H". I still need the values in the column.
* didn't paste special to Column "I" and changes format.

 

[DanteAmor]

Move Column ("H:H") to Column ("I:I")

???

* Removed Values in column "H". I still need the values in the column.
* didn't paste special to Column "I" and changes format.

Try this:

Sub moveValue()
  Dim i As Long
  Application.ScreenUpdating = False
  For i = 2 To Range("C" & Rows.Count).End(3).Row
    If Range("C" & i).Value = "S" Or Range("C" & i).Value = "V" Then Range("I" & i).Value = Range("H" & i).Value
  Next
  Application.ScreenUpdating = True
End Sub

 

[kitsa]

???




Try this:

Sub moveValue()
  Dim i As Long
  Application.ScreenUpdating = False
  For i = 2 To Range("C" & Rows.Count).End(3).Row
    If Range("C" & i).Value = "S" Or Range("C" & i).Value = "V" Then Range("I" & i).Value = Range("H" & i).Value
  Next
  Application.ScreenUpdating = True
End Sub

Hi Dante,
Thank you that worked but how do I have the same format as original e.g. Accounting. I'm not sure where to apply "PasteSpecial xlPasteValuesAndNumberFormats". Need to look like pic attached in Brown.

 

[Alex Blakenburg]

If you are going to use images you need to include the Row and Column references.
Is there any reason you don't apply the Number Formatting to the whole column or at least each whole section up front ?
Applying number formatting inside the loop on a row by row basis would be the easiest but a bad idea.

Something like this might work. This is mostly using @DanteAmor's solution.

Sub moveValue_Mod()
  Dim i As Long
  Dim NumFmt As String
 
  NumFmt = ""
 
  Application.ScreenUpdating = False
  For i = 2 To Range("C" & Rows.Count).End(3).Row
    If Range("C" & i).Value = "S" Or Range("C" & i).Value = "V" Then
        Range("I" & i).Value = Range("H" & i).Value
        If NumFmt = "" Then NumFmt = Range("H" & i).NumberFormat
    End If
  Next
 
  Range("I2:I" & Range("H" & Rows.Count).End(3).Row).NumberFormat = NumFmt
 
  Application.ScreenUpdating = True
End Sub

 

[DanteAmor]

I'm not sure where to apply "PasteSpecial xlPasteValuesAndNumberFormats".

I show you 2 options:

Option 1:

Sub moveValue_v1()
  Dim i As Long
  Application.ScreenUpdating = False
  For i = 2 To Range("C" & Rows.Count).End(3).Row
    If Range("C" & i).Value = "S" Or Range("C" & i).Value = "V" Then
      Range("H" & i).Copy
      Range("I" & i).PasteSpecial xlPasteFormulasAndNumberFormats
    End If
  Next
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

Option 2:

Sub moveValue_v2()
  Dim i As Long
  Application.ScreenUpdating = False
  For i = 2 To Range("C" & Rows.Count).End(3).Row
    If Range("C" & i).Value = "S" Or Range("C" & i).Value = "V" Then
      Range("H" & i).Copy Range("I" & i)
    End If
  Next
  Application.ScreenUpdating = True
End Sub

 

[kitsa]

I show you 2 options:

Option 1:

Sub moveValue_v1()
  Dim i As Long
  Application.ScreenUpdating = False
  For i = 2 To Range("C" & Rows.Count).End(3).Row
    If Range("C" & i).Value = "S" Or Range("C" & i).Value = "V" Then
      Range("H" & i).Copy
      Range("I" & i).PasteSpecial xlPasteFormulasAndNumberFormats
    End If
  Next
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

Option 2:

Sub moveValue_v2()
  Dim i As Long
  Application.ScreenUpdating = False
  For i = 2 To Range("C" & Rows.Count).End(3).Row
    If Range("C" & i).Value = "S" Or Range("C" & i).Value = "V" Then
      Range("H" & i).Copy Range("I" & i)
    End If
  Next
  Application.ScreenUpdating = True
End Sub

Thanks Dante,
Works great.

 

[kitsa]

If you are going to use images you need to include the Row and Column references.
Is there any reason you don't apply the Number Formatting to the whole column or at least each whole section up front ?
Applying number formatting inside the loop on a row by row basis would be the easiest but a bad idea.

Something like this might work. This is mostly using @DanteAmor's solution.

Sub moveValue_Mod()
  Dim i As Long
  Dim NumFmt As String
 
  NumFmt = ""
 
  Application.ScreenUpdating = False
  For i = 2 To Range("C" & Rows.Count).End(3).Row
    If Range("C" & i).Value = "S" Or Range("C" & i).Value = "V" Then
        Range("I" & i).Value = Range("H" & i).Value
        If NumFmt = "" Then NumFmt = Range("H" & i).NumberFormat
    End If
  Next
 
  Range("I2:I" & Range("H" & Rows.Count).End(3).Row).NumberFormat = NumFmt
 
  Application.ScreenUpdating = True
End Sub

Hi Alex,
Sorry but this stops at "End If".

 

[Alex Blakenburg]

Just curious, what do you mean by it stops at End If ?