MACRO to change the format of all worksheets into a specified format

[usama.anwar]

Hello Everyone,

I am new here and new to MACROs. I need help on a problem that I am facing. I have a workbook that has around 334 worksheets. These worksheets needs to be uploaded to a datawarehouse (DWH). There format is not similar to what a DWH accepts in order to display normal DWH tables.

I have developed a format which shall be uploaded to DWH. But I have worked on one worksheet only and I want to convert the rest of the worksheets into this format. I am attaching a sample file with the format that I have created and there are 4 more sheets in the original format which need to be changed to the format of first sheet.

First sheet name is Latifabad - Format
Second Sheet - Qasimabad
Third Sheet - Gari Khatta
Fourth sheet - Cloth Markt
Fifth Sheet - Saddar, Hyderabad

Hoping for a positive response guys. Thank You so much.

Usama

[humdingaling]

Here is the code to loop all worksheets

Please Login or Register  to view this content.

you will need to come up with the code to format one sheet yourself, it looks like grunt work which i wont have time for
suggest you use the record macro several times in chunks to define the macro code for one sheet

[usama.anwar]

Thank you for your reply, I am pretty much new to do this so you would have to guide me plz on how to do it. I copied the whole worksheet and pasted it in another new worksheet while recording the MACRO. Then I stopped and just to test it and played the macro in another new worksheet. It did not work and gave me an pasting macro runtime error. Does this mean I have to create the whole format again while recording, and creating means that I would have to type the whole thing again while recording? Or the pasting format thing works and I am making some blunder?

thank you once again!

[humdingaling]

you need to record how you got to the new format
ie insert column in A etc etc

anyway...
i relooked at the format
would it not be easier just to rename the sheet name and change the branch name instead of changing every sheet format?

[usama.anwar]

This is a sample file, with no figures, the column starting from Jan.13 consists of budget figures for this year, monthly, the figures are different in every sheet as is the budget of every branch.

[humdingaling]

in that case, the original suggestion applies

step by step macro recording and adjusting as you go along
since you are dealing with financials you need to do it yourself to be sure everything works and your are not deleting any important lines

[usama.anwar]

Dear HumDingaling, hope you are fine.

My problem remains unfortunately. I have another small idea and I need your help plz. I have attached the format, its the original format that actually needs to be changed. Can you create a MACRO that deletes specifically defined rows. In all worksheets I want to delete rows number 2, 26, 70, 81, 95, 106, 126, 139, 191, 236, 286, 356, 359, 374, 383, 436, 494, 507, 528, but the first cell of that row has the heading so I do not want that to be deleted. So is it possible that information in these row numbers are deleted from column B to M. Thank you so much

One more small thing, there is something wrong with the page setup i guess i can not add rows or columns and the whole worksheet except the cells with words and value is grey.

Thank you so much for your help and advice

[tehneXus]

Hi usama.anwar,

delete rows number 2, 26, 70, 81, 95, 106, 126,...

Code to remove specific cells for your case (done in attached document):

Please Login or Register  to view this content.

there is something wrong with the page setup i guess i can not add rows or columns and the whole worksheet except the cells with words and value is grey

The columns/rows are hidden, just click the triangle left hand side of the column number A and above the row number 1, then rightclick a column number (e.g. A) and unhide, same for rows.

For your problem with the formatting I'd suggest you create one template sheet which will then be copied to other sheets:

Please Login or Register  to view this content.

See this file: Budget Format working plan 2.xlsm