Hello,
I'm new to macro creation and I've been using this macro to extract certain columns from an excel file, copying them to a new workbook and saving it into a specific location.
in case of adding a new column in between, it will mess up the selected range of columns (A:A, Z:Z, DB:DB, DC:DC).
the purpose of the change is to read 4 specific column headers, instead of the mentioned range and execute the rest of the macro.
also, after the creation of the new workbook, is it possible to send the new workbook as an attachment with Outlook?
1. Find and Extract 4 specific columns by header name (new)
2. paste to new workbook
3. save workbook with value of cell A2 in specific location
4. send as attachment with outlook to specific email (new)
Many Thanks in advance if anyone can help me with this one.
I'm new to macro creation and I've been using this macro to extract certain columns from an excel file, copying them to a new workbook and saving it into a specific location.
in case of adding a new column in between, it will mess up the selected range of columns (A:A, Z:Z, DB:DB, DC:DC).
the purpose of the change is to read 4 specific column headers, instead of the mentioned range and execute the rest of the macro.
also, after the creation of the new workbook, is it possible to send the new workbook as an attachment with Outlook?
1. Find and Extract 4 specific columns by header name (new)
2. paste to new workbook
3. save workbook with value of cell A2 in specific location
4. send as attachment with outlook to specific email (new)
Many Thanks in advance if anyone can help me with this one.
VBA Code:
Public Sub Extract_columns()
Sheets("HCP Report").Select
Set range1 = Range("A:A, Z:Z, DB:DB, DC:DC")
range1.Copy
Set newbook = Workbooks.Add
ActiveCell.PasteSpecial Paste:=xlPasteValues
Dim Path As String
Dim Filename As String
Path = "sharepointLocation"
Filename = Range("A2")
ActiveWorkbook.SaveAs Filename:=Path & Filename & ".xlsx", FileFormat:=51
End Sub