VBA: Copy-Paste Row Subject to value in column A

[Adar123]

Hi all,

I am trying to write a logic, where if cell value in column A = "A", the data from that whole row needs to be copied to the lastrow +1 / the first empty row below. As a step 2, the values of cells in column A within the newly copied range or the original range of cells with value A to replaced with value "D".

Thank you.

The code which is incomplete:

lastRow = Cells(Rows.Count, 1).End(xlUp).Row 
    ' Loop through each row
    For x = 2 To lastRow
        ThisValue = Cells(x, 4).Value
        If ThisValue = "A" Then
            Cells(x, 1).Resize(Y, 1).Copy 'I am trying to copy the whole range in one go because lines of data with value "A" in cells in Column A will be one after another but haven't yet defined "Y"
                        NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
        End If
     Next x

The dataset sample

Commodity	Pillar	Price
A	Sep20​	3.485​
A	Oct20​	3.5775​
A	Nov20​	3.5775​
B	Jan22​	9.415​
B	Feb22​	9.265​
B	Mar22​	9.265​
B	Apr22​	9.2375​
B	May22​	9.2375​
C	Mar27​	6.1033​
C	Apr27​	6.1101​
C	May27​	6.1101​
C	Jun27​	6.1133​
C	Jul27​	6.1133​

 

[Peter_SSs]

See if this does what you want. Test with a copy of your data.

Sub CopyAndChange()
  Dim oset As Long, Rws As Long
  
  Application.ScreenUpdating = False
  With Range("A1").CurrentRegion
    oset = .Rows.Count + 1
    .AutoFilter Field:=1, Criteria1:="A"
    Rws = .Columns(1).SpecialCells(xlVisible).Count - 1
    If Rws > 0 Then
      .Offset(1).Resize(Rws).Copy Destination:=.Cells(oset, 1)
      .Cells(oset, 1).Resize(Rws).Value = "D"
    End If
    .AutoFilter Field:=1
  End With
  Application.ScreenUpdating = True
End Sub

 

[Adar123]

Thank you, this seem to do the job but how you disable filter after the job is done? I am mindful that it may create some issues once I start copying the data and doing other changes to it.

 

[Peter_SSs]

how you disable filter after the job is done?

Make this change

.AutoFilter Field:=1
.Parent.AutoFilterMode = False

 

[Adar123]

Thank you. With further testing I realized that it is picking data from a wrong line. If you run it against Criteria1:="B" or Criteria1:="C" it take values from columns B:C incorrectly.

 

[Peter_SSs]

I realized that it is picking data from a wrong line.

Yes, sorry, my mistake. Test this instead.

Sub CopyAndChange_v2()
  Dim oset As Long, Rws As Long
  
  Application.ScreenUpdating = False
  With Range("A1").CurrentRegion
    oset = .Rows.Count + 1
    .AutoFilter Field:=1, Criteria1:="A"
    Rws = .Columns(1).SpecialCells(xlVisible).Count - 1
    If Rws > 0 Then
      .Offset(1).Resize(oset - 2).Copy Destination:=.Cells(oset, 1)
      .Cells(oset, 1).Resize(Rws).Value = "D"
    End If
  .Parent.AutoFilterMode = False
  End With
  Application.ScreenUpdating = True
End Sub

 

[Adar123]

Thank you, this is working perfect now but need to scratch my head a bit to fully understand the syntax.

 

[Peter_SSs]

Cheers. glad it is working for you.