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
Sir
One more point sir, when i am running 4th macro i.e combining 1 to 3 macros, data is correctly transferred to the respective sheets but only formatting part is not coming correctly.
with regards
kishore
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.
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
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.
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
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
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.
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.
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.
The code is as follows
sub "macro name"
call "macro1"
call "macro2"
call "macro3"
end sub
One more point sir, when i am running 4th macro i.e combining 1 to 3 macros, data is correctly transferred to the respective sheets but only formatting part is not coming correctly.
with regards
kishore