Help with VBA

saroo970

New Member
Joined
Oct 4, 2021
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
I have 100 excel sheets all of them are identical with seven columns (Date, Open, High, Low, Close, Adj Close, Volume) what I need is to

1- delete all the columns in all 100 excel sheets inside the folder but keep only two columns (“Date”, and the “Close”)
2- I need to format all the 100 sheets the first column “Date” columns to be (dd-mmm-yyyy) and the “close” columns to be in this format 0.00000 which means the number should be like this 5.12345.

Thanks,
Sam
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,916
Office Version
  1. 2013
Platform
  1. Windows
Yes, definitely true. I hope someone can help. it can be done manually but as you can see it is very hard to do one by one. Appreciate your input and trying to help
Did you try my script in a open workbook? And did it do what you wanted?
It loops through all the sheets in the open workbook.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

saroo970

New Member
Joined
Oct 4, 2021
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
I tried it but it deletes the last columns always, it might need some update to the code so it can delete the selected columns which in my case it is (B, C, D, F, and G). Attached is the final result of what I am looking for
 

Attachments

  • Capture4.PNG
    Capture4.PNG
    23.3 KB · Views: 2

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,916
Office Version
  1. 2013
Platform
  1. Windows
I tried it several times and it deletes column 2,3,4,6,7
In all sheets.
So not sure why it does not work for you
 

saroo970

New Member
Joined
Oct 4, 2021
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
Now it worked perfectly, no idea what did. Definitely, it was me (result attached after running your script - Wonderful results
 

Attachments

  • Capture555.PNG
    Capture555.PNG
    39.8 KB · Views: 2

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,916
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Now it worked perfectly, no idea what did. Definitely, it was me (result attached after running your script - Wonderful results
Glad to here that. Now just waiting for someone here to write a script to do this on all the workbooks you have in some particular folder.
 

saroo970

New Member
Joined
Oct 4, 2021
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
Hope the code you provided will be updated to include all the workbooks in the folder
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,916
Office Version
  1. 2013
Platform
  1. Windows
Hope the code you provided will be updated to include all the workbooks in the folder
Yes I was hoping someone else would help you with this post. I know how to do what you want with a Open book but not sure how to do what you want with all the Workbooks in a particular folder. Fluff made a few postings in this thread so I was hoping Fluff may be able to help you.
 

Forum statistics

Threads
1,143,688
Messages
5,720,305
Members
422,275
Latest member
Maria95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top