VBA - How to copy and paste the entire column based on a condition?

[nevers711]

Hi everyone, I am pretty new to VBA. Can anybody help me with my code?

I have an excel file, and sheet1 contains the source data. Please see attached

I would like to use Macro to help me copy the entire column of "PO #", and paste the entire "PO #"column to Sheet2's cell A1.. The position of "PO #" may vary. It may not be at Column C, so I need to ask Macro to help me locate that column

Here is the code I wrote but it doesn't work.. It only copies the column where my mouse is at... not sure what is wrong



Sub Macro1()

Dim j As Long
For j = 1 To 8
Sheets("Sheet1").Activate
If Sheets("Sheet1").Cells(j, 1) = "PO #" Then Sheets("Sheet1").Cells(j, 1).Select
Selection.EntireColumn.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next j

End Sub

 

[SidharthS]

you are looping through rows instead of columns and all the line of codes for copying and pasting should be placed inside if function

i have only altered what was most needed this can be more trimmed down.


Sub Macro1()

Dim j As Long
For j = 1 To 8
Sheets("Sheet1").Activate
If Sheets("Sheet1").Cells(1, j) = "PO #" Then
Sheets("Sheet1").Cells(1, j).Select
Selection.EntireColumn.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next j

End Sub

 

[nevers711]

you are looping through rows instead of columns and all the line of codes for copying and pasting should be placed inside if function

i have only altered what was most needed this can be more trimmed down.


Sub Macro1()

Dim j As Long
For j = 1 To 8
Sheets("Sheet1").Activate
If Sheets("Sheet1").Cells(1, j) = "PO #" Then
Sheets("Sheet1").Cells(1, j).Select
Selection.EntireColumn.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next j

End Sub

OMG! Thank you so much for finding this out!! I didn't notice this issue!!