Good Day Pros, Please can anybody put me in right direction, I was trying to select multiple columns by the column header in excel sheet, then copy these columns into a new work book, when i do this with the code below, the excel open one book per column instead of pasting all of the selected columns into sheet1 of the new workbook. Please help. (I have come across different solutions, i just didnt get how to make them work for me, i guess due to lack of experience, i recorded a macro for this task which worked well, but the problem is that the column header changes every time so i can not depend on recorded macros). Thank you.
Sub Colheadr()
Dim wsO As Worksheet
'Dim wsF As Worksheet....I comment out this line
Dim i As Integer
Application.ScreenUpdating = False
Set wsO = ActiveSheet
'Set wsF = Worksheets("Final").....I comment out this line
myColumns = Array("Facility", "Last Name", "First Name", "MRN", "adm date")
With wsO.Range("A1:W1")
For i = 0 To UBound(myColumns)
On Error Resume Next
.Find(myColumns(i)).EntireColumn.Copy
Workbook.Add
ActiveSheet.Paste
'Destination:=wsF.Cells(1, i + 1)...I comment out this line
Err.Clear
Next i
End With
Set wsO = Nothing
Set wsF = Nothing
Application.ScreenUpdating = True
End Sub
