Combining three different macros
Closed
kishore
-
Jan 7, 2012 at 10:52 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 15, 2012 at 08:59 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 15, 2012 at 08:59 PM
Related:
- Combining three different macros
- Macros in excel download - Download - Spreadsheets
- Do macros work in excel online - Guide
- Unlock excel vba and excel macros - Guide
- Enable macros in excel 365 - Guide
- How to create a calculator in excel using macros - Guide
12 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 8, 2012 at 12:17 AM
Jan 8, 2012 at 12:17 AM
how is the fourth macro worded?
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 8, 2012 at 08:46 AM
Jan 8, 2012 at 08:46 AM
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
sub macro_name"
macro1
macro2
macro3
end sub
name of the macro should be one word. call may not be necessary
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 8, 2012 at 11:29 AM
Jan 8, 2012 at 11:29 AM
"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
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
Didn't find the answer you are looking for?
Ask a question
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
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
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 8, 2012 at 11:14 PM
Jan 8, 2012 at 11:14 PM
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
http://www.speedyshare.com/
and post the webpage for downloading the file
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
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
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 9, 2012 at 09:37 PM
Jan 9, 2012 at 09:37 PM
I tested your main macro "update" it works alright without any interruption.You have written the macro correctly. in my computer this code
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
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 .
.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 .
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
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
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 15, 2012 at 04:39 AM
Jan 15, 2012 at 04:39 AM
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.
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
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
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 15, 2012 at 08:59 PM
Jan 15, 2012 at 08:59 PM
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
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
Jan 8, 2012 at 08:30 AM
The code is as follows
sub "macro name"
call "macro1"
call "macro2"
call "macro3"
end sub
Jan 8, 2012 at 09:03 AM
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