Hello,
I am new to macros and VBA so I apologize for my poor explanations. The issue is that the spread sheet I'm working with has dates which are incorrectly read and filtered improperly. When I go to filter the dates, it lists every single date in the column instead of by year, month, and then day. The way to fix it is listed below but I would like to automate this process using a macro. I have listed a TL;DR version first and a more detailed explanation second. Thanks for all the help.
TL;DR version:
1.) Parse through row 1 and find the string "date"
2.) Copy the entire column
3.) Insert a new column after the copied column
4.) Paste as special, paste "Add" into the new column
5.) Select the new column
6.) Reformat the cells as "Date"
7.) Copy the header from the original column into the new column
8.) Delete the original column's data and shift the new columns information into the original column
9.) Repeat until it reaches the end of row 1
OPTIONAL - 10) Copy all of the contents and move them to a new spread sheet in the same work book
Detailed version:
I currently have a spread sheet which has headers in row A1 until the end of the spread sheet. I want to identify the columns which contain the string "date" in the header. I then want to take the entire column and copy it. I want to insert a new column after the copied column, as in if I select column "E" I want to insert a new blank column at "F". I then want to paste special and paste as "Add". Then I want to select the entire column and select format cells and format the column as "Date". Then I want to copy over the header in the new column's row 1 spot, as in the header in "E1" moves to the "F1" header. After this, the row that was incorrectly formatted may be deleted, so following my example, I would delete all of the contents in "E" and the information should shift over from "F" to "E". I want to do this to multiple columns so I need it to parse through the entire header row. If possible, I would like to copy this and move it to a new spread sheet as well.</SPAN>
I am new to macros and VBA so I apologize for my poor explanations. The issue is that the spread sheet I'm working with has dates which are incorrectly read and filtered improperly. When I go to filter the dates, it lists every single date in the column instead of by year, month, and then day. The way to fix it is listed below but I would like to automate this process using a macro. I have listed a TL;DR version first and a more detailed explanation second. Thanks for all the help.
TL;DR version:
1.) Parse through row 1 and find the string "date"
2.) Copy the entire column
3.) Insert a new column after the copied column
4.) Paste as special, paste "Add" into the new column
5.) Select the new column
6.) Reformat the cells as "Date"
7.) Copy the header from the original column into the new column
8.) Delete the original column's data and shift the new columns information into the original column
9.) Repeat until it reaches the end of row 1
OPTIONAL - 10) Copy all of the contents and move them to a new spread sheet in the same work book
Detailed version:
I currently have a spread sheet which has headers in row A1 until the end of the spread sheet. I want to identify the columns which contain the string "date" in the header. I then want to take the entire column and copy it. I want to insert a new column after the copied column, as in if I select column "E" I want to insert a new blank column at "F". I then want to paste special and paste as "Add". Then I want to select the entire column and select format cells and format the column as "Date". Then I want to copy over the header in the new column's row 1 spot, as in the header in "E1" moves to the "F1" header. After this, the row that was incorrectly formatted may be deleted, so following my example, I would delete all of the contents in "E" and the information should shift over from "F" to "E". I want to do this to multiple columns so I need it to parse through the entire header row. If possible, I would like to copy this and move it to a new spread sheet as well.</SPAN>
