Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi all, Back again with new problem. I have recorded a macro to update the file every week, but it is too lengthy to understand or make any change. Just want to shorten it as much as possible. Pl help.
    Attached Files Attached Files
    Regards
    Prasad

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    On cursory examination the use of "selection" can be removed

    Any time you have

    xxxx.Select
    Selection.yyyy

    You can change to
    xxxx.yyyy

    It would be helpful if you told us the purpose of the code and what it is expected to do if you want help other help with it.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='780978' date='22-Jun-2009 11:32']On cursory examination the use of "selection" can be removed

    Any time you have

    xxxx.Select
    Selection.yyyy

    You can change to
    xxxx.yyyy

    It would be helpful if you told us the purpose of the code and what it is expected to do if you want help other help with it.

    Steve[/quote]

    I have a workbook of outstanding receivable of current week comparison with last 2 weeks. Three are 2 summary sheets & 10 sheets pertaining to different units. On every new week, I have to update the data in summary sheet called “BG3”, which is linked with “units” sheets. To do it automatically is the only purpose to record the macro. Sample sheet is attached for reference. Help/suggestion would be highly appreciated.
    Attached Files Attached Files
    Regards
    Prasad

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    [quote name='prasad' post='780991' date='22-Jun-2009 08:25']To do it automatically is the only purpose to record the macro. Sample sheet is attached for reference. Help/suggestion would be highly appreciated.[/quote]

    I understand you want to do it automatically. The question is what do you want to do automatically. What are the steps the code should follow and the logic?

    If the code does what you want I would recommend (as I said before) to eliminate the selecting and I would consider some looping, but without details on the logic it is difficult to make changes to the logic in your code.

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='781300' date='23-Jun-2009 19:21']I understand you want to do it automatically. The question is what do you want to do automatically. What are the steps the code should follow and the logic?

    If the code does what you want I would recommend (as I said before) to eliminate the selecting and I would consider some looping, but without details on the logic it is difficult to make changes to the logic in your code.[/quote]

    Thanks Steve for understand my problem.

    Here are the steps I have taken while recording macro:

    1. In Sheet BG3 :
    Cell T1=R1+7, then copy/paste value
    Copy this value & paste in R1, this will change the weekended date whereever applicable
    Copy/paste value the data of current week of Sales, Receipts & Due (which is actually relates to last weeks) from column M, P & R to N,Q & S and form D,H,K to M, P, R respectively (in present sheet, from week 24 to week 23 and Week 25 to 24). Week nos are mentioned in Row 4 of respective columns.
    Copy the sum formula from K 10 and paste special - formula to M10, N10,P10, Q10, R10,S10, M14,N14 …till N 53 (total of every unit, since values are pasted)

    2. In sheet Gr. Summary
    Copy data from D16:E22 and paste value to E16:F22
    Copy data from I16:J22 and paste value to J16:K22
    Copy data from N16:O22 and paste value to O16:P22

    and so on for all 12 units

    3. In support sheets of units N1,N2……..
    Copy data (figures only) from column L and paste value to Column S. In all cases data starts from L5 but ends on dirrerent rows, depends upon volume.


    Current recorded macro is doing well in Sheet BG3 & Gr. Summary, but sometimes missed to copy/paste the data in units sheets and I have to check every sheet to validate it. In addition to that I am also looking to incorporate a code to highlights the top 4 +ve value in red pattern with white bold font and top 4 -ve values with green pattern with white bold font in units sheets. At present, doing this with conditional formating without satisfaction.

    Is it making any sense or I am just wasting your precious time?

    Regards
    Prasad

  6. #6
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='prasad' post='781406' date='24-Jun-2009 05:56']Thanks Steve for understand my problem.

    Here are the steps I have taken while recording macro:

    1. In Sheet BG3 :
    Cell T1=R1+7, then copy/paste value
    Copy this value & paste in R1, this will change the weekended date whereever applicable
    Copy/paste value the data of current week of Sales, Receipts & Due (which is actually relates to last weeks) from column M, P & R to N,Q & S and form D,H,K to M, P, R respectively (in present sheet, from week 24 to week 23 and Week 25 to 24). Week nos are mentioned in Row 4 of respective columns.
    Copy the sum formula from K 10 and paste special - formula to M10, N10,P10, Q10, R10,S10, M14,N14 …till N 53 (total of every unit, since values are pasted)

    2. In sheet Gr. Summary
    Copy data from D16:E22 and paste value to E16:F22
    Copy data from I16:J22 and paste value to J16:K22
    Copy data from N16:O22 and paste value to O16:P22

    and so on for all 12 units

    3. In support sheets of units N1,N2……..
    Copy data (figures only) from column L and paste value to Column S. In all cases data starts from L5 but ends on dirrerent rows, depends upon volume.


    Current recorded macro is doing well in Sheet BG3 & Gr. Summary, but sometimes missed to copy/paste the data in units sheets and I have to check every sheet to validate it. In addition to that I am also looking to incorporate a code to highlights the top 4 +ve value in red pattern with white bold font and top 4 -ve values with green pattern with white bold font in units sheets. At present, doing this with conditional formating without satisfaction.

    Is it making any sense or I am just wasting your precious time?

    [/quote]

    Hi Steve, still looking for some clue otherwise i will have to hang with same old stuff as weekend is coming
    Regards
    Prasad

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Why don't you clean up the code as I suggested, getting rid of the select/selection select/Activecell combinations and even all the scrolling which are unneeded. You can also eliminate the all the "Application.CutCopyMode = False" and just have one at the end.

    Once you clean that up look at the things your are copying and pasting and look at the patterns to work out the loops instead of the individually repeated commands. These things will clean up your code greatly.

    And as to

    but sometimes missed to copy/paste the data in units sheets and I have to check every sheet to validate it.
    You will have to expand on what is not working and how the code can tell when it is not working

    In addition to that I am also looking to incorporate a code to highlights the top 4 +ve value in red pattern with white bold font and top 4 -ve values with green pattern with white bold font in units sheets
    You will have to explain in more detail what you want to code to look through and how it should highlight. What values should be checked and what they should all be compared with.

    Steve

  8. #8
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='781774' date='25-Jun-2009 19:38']Why don't you clean up the code as I suggested, getting rid of the select/selection select/Activecell combinations and even all the scrolling which are unneeded. You can also eliminate the all the "Application.CutCopyMode = False" and just have one at the end.

    Once you clean that up look at the things your are copying and pasting and look at the patterns to work out the loops instead of the individually repeated commands. These things will clean up your code greatly.

    And as to

    You will have to expand on what is not working and how the code can tell when it is not working

    You will have to explain in more detail what you want to code to look through and how it should highlight. What values should be checked and what they should all be compared with.

    Steve[/quote]

    Thanks Steve,

    As suggested, I have made some changes. Working fine but still needs some improvements. Now it looks like :
    [codebox]
    Sub changeweek()
    '
    ' changeweek

    ' Keyboard Shortcut: Ctrl+Shift+W
    '
    Range("R1").Copy
    Range("T1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("R1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[2]+7"
    Range("R1").Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("M8:M33").Copy
    Range("N8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("P8:P33").Copy
    Range("Q8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("R8:R33").Copy
    Range("S8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("M43:M53").Copy
    Range("N43").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("P43:P53").Copy
    Range("Q43").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("R43:R53").Copy
    Range("S43").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("D833").Copy
    Range("P8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("H8:H33").Copy
    Range("R8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("K8:K33").Copy
    Range("M8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("D4353").Copy
    Range("P43").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("H43:H53").Copy
    Range("R43").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("K43:K53").Copy
    Range("M43").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("M10").FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
    Range("M10").Copy
    Range("N10:S10").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range("M14:S14").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range("M18:S18").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range("M22:S22").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range("M26:S26").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range("M30:S30").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range("M34:S34").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range("M45:S45").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range("M49:S49").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range("M53:S53").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range("K40").Copy
    Range("M40:S40").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Sheets("N1").Select
    Range("L4:L200").Copy
    Range("S4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("A4").Select
    Sheets("N2").Select
    Range("L4:L200").Copy
    Range("S4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("A4").Select
    Sheets("N3").Select
    Range("L4:L200").Copy
    Range("S4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("A4").Select
    Sheets("N8").Select
    Range("L4:L200").Copy
    Range("S4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("A4").Select
    Sheets("N9").Select
    Range("L4:L200").Copy
    Range("S4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("A4").Select
    Sheets("ANS").Select
    Range("L4:L200").Copy
    Range("S4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("A4").Select
    Sheets("ANTB").Select
    Range("L4:L200").Copy
    Range("S4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("A4").Select
    Sheets("N4").Select
    Range("L4:L200").Copy
    Range("S4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("A4").Select
    Sheets("N5").Select
    Range("L4:L200").Copy
    Range("S4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("A4").Select
    Sheets("N6").Select
    Range("L4:L200").Copy
    Range("S4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("A4").Select
    Sheets("Gr Summary").Select
    Range("D16:E22").Copy
    Range("E16").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
    IconFileName:=False
    Range("I16:J22").Copy
    Range("J16").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
    IconFileName:=False
    Range("N16:O22").Copy
    Range("O16").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
    IconFileName:=False
    Range("S16:T22").Copy
    Range("T16").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
    IconFileName:=False
    Range("D27:E33").Copy
    Range("E27").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
    IconFileName:=False
    Range("I27:J33").Copy
    Range("J27").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
    IconFileName:=False
    Range("N27:O33").Copy
    Range("O27").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
    IconFileName:=False
    Range("S27:T33").Copy
    Range("T27").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
    IconFileName:=False
    Range("D38:E44").Copy
    Range("E38").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
    IconFileName:=False
    Range("I38:J44").Copy
    Range("J38").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
    IconFileName:=False
    Range("N38:O44").Copy
    Range("O38").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
    IconFileName:=False
    Range("S38:T44").Copy
    Range("T38").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
    IconFileName:=False
    Range("B5").Select
    Sheets(Array("N1", "N2", "N3", "N8", "N9", "ANS", "ANTB", "N4", "N5", "N6")).Select
    Sheets("N1").Activate
    ActiveWorkbook.Sheets("N6").Tab.ColorIndex = -4142
    ActiveWorkbook.Sheets("N5").Tab.ColorIndex = -4142
    ActiveWorkbook.Sheets("N4").Tab.ColorIndex = -4142
    ActiveWorkbook.Sheets("ANTB").Tab.ColorIndex = -4142
    ActiveWorkbook.Sheets("ANS").Tab.ColorIndex = -4142
    ActiveWorkbook.Sheets("N9").Tab.ColorIndex = -4142
    ActiveWorkbook.Sheets("N8").Tab.ColorIndex = -4142
    ActiveWorkbook.Sheets("N3").Tab.ColorIndex = -4142
    ActiveWorkbook.Sheets("N2").Tab.ColorIndex = -4142
    ActiveWorkbook.Sheets("N1").Tab.ColorIndex = -4142
    Sheets("BG3 ").Select
    Range("A3:A6").Select
    End Sub
    [/codebox]
    Any Comments/suggestion???
    Regards
    Prasad

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You still have all these combinations

    Range(...).Select
    Selection.PasteSpecial ...

    Please follow Steve's suggestion to change them to

    Range(...).PasteSpecial ...

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You still haven't made all I suggested (Hans pointed out that you still have some Select/Selection combinations) but there are also some "ActiveCell" and even "Application.CutCopyMode = False"

    Steve

  11. #11
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='782020' date='27-Jun-2009 11:05']You still haven't made all I suggested (Hans pointed out that you still have some Select/Selection combinations) but there are also some "ActiveCell" and even "Application.CutCopyMode = False"

    Steve[/quote]

    Thanks Hans/Steve for your valuable comments. What abt this one ?
    Attached Files Attached Files
    Regards
    Prasad

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You apply the same changes to sheets N1, N2 etc. You could do that in a loop:

    Code:
      Dim varSheet
      For Each varSheet In Array("N1", "N2", "N3", "N4", "N5", "N6", "N8", "N9", "ANS", "ANTB")
    	With Worksheets(varSheet)
    	  ' Copy some values
    	  .Range("L4:L200").Copy
    	  .Range("S4").PasteSpecial Paste:=xlPasteValues
    	  .Range("A4").Select
    	  ' Uncolor the sheet tab
    	  .Tab.ColorIndex = xlColorIndexNone
    	End With
      Next varSheet

  13. #13
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='prasad' post='782027' date='27-Jun-2009 12:41']Thanks Hans/Steve for your valuable comments. What abt this one ? [/quote]

    Pl also suggest some code to highlight changes between 2 figures. Presently i am doing the same through conditional formating.Sample sheet is attahced for reference. This requirement is part of "changeweek" code.

    Thanks
    Attached Files Attached Files
    Regards
    Prasad

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What's wrong with conditional formatting?

  15. #15
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='782034' date='27-Jun-2009 13:28']What's wrong with conditional formatting?[/quote]
    This is wrt your earlier suggestion. I have incorporated the code (Copy attached) & “Runtime error-1004 select method of range failed” message is appearing. On debugging, it highlight .”Range("A4").Select”.

    Regarding conditional formatting, if you suggest, i will go with it.
    Attached Files Attached Files
    Regards
    Prasad

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •