Combining three different macrosClosed

Question

Sir, 
I am a beginner  in writing  codes(macro's). With great difficulty i have written three different macro's to copy data from main sheet to  three different sheets. I am also formatting the data in the new three sheets with same macro's.

Instead of running three different macros,  i have written another 4th macro to combine all the three macro's. When i am running the 4th macro, i am not getting the correct data, some times data is copied correctly but the formatting part is not coming properly.  when i am using one macro at a time all the three macros are running perfectly.  Can anybody help me in this regard. 
With regards

Kishore

venkat1926 · Answer

how is the fourth macro worded?

venkat1926 · Answer

I think it should be(try )

sub macro_name"
macro1
macro2
macro3
end sub

name of the macro should be one word. call may not be necessary

kishore · Answer

Dear venkat
I tried by removing "call" also but it is not working.

Kishore

venkat1926 · Answer

"but only formatting part is not coming correctly. "
depends upon the code. how is the data transferred by a formula or copy paste etc.

post a small example of code. 
a trivial example is 

Sub test()
Range("a1").Copy
Range("a20").PasteSpecial xlPasteAll
End Sub

Kishore · Answer

Dear Venkat

I am copying data through Macros(i.e macro1,macro2, & Macro3). Long back u have helped me writing the three codes. In Macro1 to Macro3, after copy code i have added formatting code. The formatting code is written by recording a new macro and do the formatting as per requirement in the sheet and after stopping the  recording,  the same code is copied in the regular macro (i.e. macro1). As i told you earlier the copy part is coming properly but the second part i.e Formatting part is not coming. I don't have full knowledge of writing codes. Actually i am going through  kioskea forum and i am learning in writing new codes.   

with regards
Kishore

venkat1926 · Answer

will it be possible for you upload the file or small extract with the macros to 
http://www.speedyshare.com/
and post the webpage for downloading the file

kishore · Answer

Dear Sir

I am uploading the file for your convenience. I have removed some sheets, only relevant sheets are kept in the file the link is below
http://speedy.sh/2dfKT/ASFOA-test.xls

In the workbook five sheets are there. "Collection" sheet is source data sheet. other sheets are derived from the "Collection" sheet. There are total 5 macros are there. Macro"upload" is combining the 4 macros. When the macros are run individually data is transferd and also formatting is done. But when macros are bunched in one macro (i.e. "Update" macro) only data is transferd and formatting part is not done as per the code written in the respective macros. My point is i am using some more macros for updating some more sheets, running each macro may be monotonous hence i want have one macro for updating all my sheets.  
Kindly help me.

Kishore

venkat1926 · Answer

I tested your main macro "update" it works alright  without any interruption.You have written the macro correctly. in my computer this code 

.TintAndShade = 0
gives me error.  (I ignore it).  I do not see the need for it when color index itself is 0.

now you say that some formats are not reflected when the combined macro is run. can you give five examples 
1) the code where you have made this
2)what cells should have what format

when you copy and paste  why do you say "xlpastevalues"  are they formulas then only this is required.
besides only if some format is there in source it can copy any format. if there is no such format there is no format to copy

if you just pastespecial xlpasteall 
this would be enough to paste the cells as it is  including color format of source cell  if any.

the macros need to be tweaked a lot. there are repetitions

one e.g

Sub pendinglist_maint()
Dim r As Range, c As Range, k As Integer, m As Integer, n As Integer
   With Worksheets("PENDING-MAINT")
.Rows.Delete
.Cells.Clear
End With

when you delete  the rows what is the need for cells.clear.

cells.clear would be enough.

besides you must also tell  what you what you want to do step by step 

the macros are OK as they are though needs lot of tweaking

post comments and feedback .

kishore · Answer

Dear Venkat

As suggested by you i have done some trimming work to my Macros. As i told you earlier still if i run the macros individually the code for copying and formatting is perfectly done and only when separate macro combing the three macro's into one macro (i.e "Update" ) the sheets are not formatted as per the code written.  All these days I have done some R & D. and  I found the following points.

1. The opened work sheet formatting is correctly formatted as per my code.
2. Other UN-opened works sheets data is transferred correctly but formatting part is not done.
3. That means when it comes to formatting part code, only active sheet  i.e. Present opened sheet where macro is executed is only formatted and other sheets are not formatted as per the code written.
I think some correction is required for the code to segregate for copying code and formatting code, specifying after first sheet is formatted then the operation should be closed and then open the next sheet then format that sheet and so on.   
I hope i have explained you correctly. 
Kindly help me 
With regards
Kishore

venkat1926 · Answer

post all the macro codes. or preferably upload the file with vb editor(macros) to speedyshare.com and post the web page for downloading the file.  If you think there are sensitive data replace with random data.

kishore · Answer

Dear Venkat

I am once again uploading the sample file 
http://speedy.sh/tp72n/ASFOA-test-1.xls.
There are Five sheets
Sheet "collection" is source data for sheet"pendinglist-all", "pendinglist-maint","pendinglist-legal", and "pendinglist-bldg". 

What i am doing is i am copying required data from "collection" sheet into 4 sheets and formatting as per my requirement for printing each sheet. 
"Update" macro is combining all the four macro into one for easy processing.
If i run the 4 macros individually all the four macro are working. when i combine only error is appearing in the macro. 
The four sheets which are uploaded are formatted as per my requirements.

with regards
Kishore

venkat1926 · Answer

I shall look into it. but 
1) is the ORDER of the macros in "update" ok .  scrutinize again
2) give at least three examples where the formats are not effected when "update" is run than when they are individually run.

when Update is run what does it do it just goes to each of those macros and run same as if you do individually (this you can check you keep cursor in update macro and continuously hit F5-this is called debugging)  but one by one in the order you have placed them in the "update". this is same as individually running the macro but in the order it is given in "update" and not as it is seen in the module 8.  when you do individual macros you are running it in the order it is written in the module.  

reply immediately best compliments

Combining three different macros

12 replies

Subscribe To Our Newsletter!