Find and Select Last Column With Data Based On ROW 1

[Buns1976]

Good Morning,
I am looking for a macro that will find the last column in my worksheet(SCAN) where Row 1 is populated, select the entire column,
copy, and paste special(VALUES) to column A in worksheet(SHIFT).

Thank You,
B

 

[DanteAmor]

Try this:

Sub copypastevalues()
  Sheets("SCAN").Columns(Sheets("SCAN").Cells(1, Columns.Count).End(1).Column).Copy
  Sheets("SHIFT").Range("A1").PasteSpecial xlPasteValues
End Sub

 

[Buns1976]

Try this:

Sub copypastevalues()
  Sheets("SCAN").Columns(Sheets("SCAN").Cells(1, Columns.Count).End(1).Column).Copy
  Sheets("SHIFT").Range("A1").PasteSpecial xlPasteValues
End Sub

Awesome!! Thank you!!

 

[Buns1976]

Try this:

Sub copypastevalues()
  Sheets("SCAN").Columns(Sheets("SCAN").Cells(1, Columns.Count).End(1).Column).Copy
  Sheets("SHIFT").Range("A1").PasteSpecial xlPasteValues
End Sub

Wondering how I would edit this macro to retrieve the 2nd to last column? I need to paste special to H1 so I'll change that part to H1

 

[DanteAmor]

I would edit this macro to retrieve the 2nd to last column?

Try this:

Sub copypastevalues()
  Sheets("SCAN").Range("B1", Sheets("SCAN").Cells(1, Columns.Count).End(1)).EntireColumn.Copy
  Sheets("SHIFT").Range("H1").PasteSpecial xlPasteValues
End Sub