Delete specific columns and sheet

[fatalcore]

Hi Friends,

I was wondering if somebody could help me out with a VBA code that could delete specific columns and all the sheets except one from all the files in a folder.
For example I have a folder with more than 50 files, with multiple sheets, but I want all the sheets to be deleted except "FinalData" in each file. Now in this sheet "FinalData" I want to delete the Columns named "S-Unit", "C-Unit", "V-Unit", "P-Unit", "Lost-Unit", "***-Unit", "M-Unit", "HIS-Unit", "Local-Unit", "Local2-Unit". then save and close.
It will be great if somebody could please help me out.
Thanks in advance,
Best regards,me.

[patel45]

For now test this macro for sheets deleting

Please Login or Register  to view this content.

[HaHoBe]

Hi, patel45,

donīt you think it would be a good idea to set

Please Login or Register  to view this content.

at the beginning of the code and

Please Login or Register  to view this content.

at the end - not every user has the warnings disabled.

Ciao,
Holger

[patel45]

many others details are missing, sorry, as you know, i'm not a good programmer

[fatalcore]

Hi Patel,
Thanks mate , the code worked and I have deleted all the sheets, can you please help me with the columns also. and thanks HaHoBe, I added the line you provided and everything was fine.
Thanks friends, you all are amazing !
Best regards,me

[HaHoBe]

Hi, fatalcore,

as long as the items only appear once you may utilize something like this:

Please Login or Register  to view this content.

Code works on the active sheet and may either be implemented into or called from within the other routine.

Ciao,
Holger

[fatalcore]

Thanks Holger,

I called the code in the above code by Patel and it worked like a charm ! thanks mate :D

But I have just realized another big problem, sorry I should have expected this earlier. can you please help me out.

Actually all my file name starts with actual user name then a space and then some junk values like for Example
1> ADAS0456 xyy478ejhn kujadikjfjk.xlsx
2> AWISTLER0566 asdlklk 03408588 kjdsf.xlsx
3> JJUNE0387 kjkj45mjdkf 48799857kjf.xlsx

what I want is after the columns are deleted can you please replace the tab name "User" to the User name like "ADAS0456" , "AWISTLER0566" , "JJUNE0387" etc. So that i can understand which user I am specifically looking.
It will be a great help mate.

Best regards,
Me.

[HaHoBe]

Hi, fatalcore,

from what I understand the code for renaming the remaining sheet may look something like this:

Please Login or Register  to view this content.

In conjunction with the code to delete columns it may look like

Please Login or Register  to view this content.

Ciao,
Holger

[fatalcore]

Waoo Holger,
That's exactly what I wanted ! all I can say Brilliant my Friend
My friends, just another query I just wanted to know is there any code that can collate the all the tabs to a single file. I mean afterward if I want to make a single file with all the user tabs. that will be a great help.
thanks mate thanks a ton for your help.
best regards,me

[HaHoBe]

Hi, fatalcore,

please have a look at Merge data from all workbooks in a folder (1), Merge data from all workbooks in a folder (2) or WBs to 1 Sheet. Or you just add the according lines of code when having renamed the tab (first opening a (new) workbook, set up the (new) worksheet (both as objects which may be used later in code without switching between workbooks) and then copy the data either below or next to each other).

Ciao,
Holger

[fatalcore]

Thanks mate ! :D
Best regards,me
marking this thread as complete.