Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a recorded macro to copy/paste values (it performs some other functions too). The problem is that it is too long (there are 56 ranges to copy from/paste to). I think it require a little bit trimming. Any suggestion

    [codebox]Range("M8:M33").Copy
    Range("N8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("P8:P33").Copy
    Range("Q8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("R8:R33").Copy
    Range("S8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False[/codebox]
    Regards
    Prasad

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    One simple option would be to create a short Subroutine that does the copy for any specified range:
    Code:
     Sub CopyValues(rngFrom As Range, rngTo As Range)
    		 rngFrom.Copy
    		 rngTo.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
     End Sub
    and then replace your code with:
    Code:
    	
    	   CopyValues Range("M8:M33"), Range("N8")
    	   CopyValues Range("P8: p33"), Range("Q8")
    	   CopyValues Range("R8: R33"), Range("S8")
    It looks as though there is a pattern to your ranges. If this continues to copy rows 8 to 33 of every second column to the next column over then we could construct a simple loop instead of a list of ranges.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You don't need to specify all arguments. Instead of

    Range("N8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    you can use

    Range("N8").PasteSpecial Paste:=xlPasteValues

  4. #4
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks StuartR
    Thanks Hans
    Implemented Hans's suggestion but not able to apply changes as suggested by StuartR. (due to my poor knowledge in VBA).

    Here is the entire code:

    [codebox]Sub changeweek()
    '
    ' changeweek

    '
    Dim varSheet
    For Each varSheet In Array("N1", "N2", "N3", "N4", "N5", "N6", "N8", "N9", "ANS", "ANTB")
    With Worksheets(varSheet)
    .Range("L4:L200").Copy
    .Range("S4").PasteSpecial Paste:=xlPasteValues
    .Tab.ColorIndex = xlColorIndexNone
    End With
    Next varSheet
    Sheets("Gr Summary").Select
    Range("D16:E22").Copy
    Range("E16").PasteSpecial Paste:=xlPasteValues
    Range("I16:J22").Copy
    Range("J16").PasteSpecial Paste:=xlPasteValues
    Range("N16:O22").Copy
    Range("O16").PasteSpecial Paste:=xlPasteValues
    Range("S16:T22").Copy
    Range("T16").PasteSpecial Paste:=xlPasteValues
    Range("D27:E33").Copy
    Range("E27").PasteSpecial Paste:=xlPasteValues
    Range("I27:J33").Copy
    Range("J27").PasteSpecial Paste:=xlPasteValues
    Range("N27:O33").Copy
    Range("O27").PasteSpecial Paste:=xlPasteValues
    Range("S27:T33").Copy
    Range("T27").PasteSpecial Paste:=xlPasteValues
    Range("D38:E44").Copy
    Range("E38").PasteSpecial Paste:=xlPasteValues
    Range("I38:J44").Copy
    Range("J38").PasteSpecial Paste:=xlPasteValues
    Range("N38:O44").Copy
    Range("O38").PasteSpecial Paste:=xlPasteValues
    Range("S38:T44").Copy
    Range("T38").PasteSpecial Paste:=xlPasteValues
    Range("B5").Select
    Sheets("BG3 ").Select
    Range("R1").Copy
    Range("T1").PasteSpecial Paste:=xlPasteValues
    Range("R1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[2]+7"
    Range("R1").Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("M8:M33").Copy
    Range("N8").PasteSpecial Paste:=xlPasteValues
    Range("P8:P33").Copy
    Range("Q8").PasteSpecial Paste:=xlPasteValues
    Range("R8:R33").Copy
    Range("S8").PasteSpecial Paste:=xlPasteValues
    Range("M43:M53").Copy
    Range("N43").PasteSpecial Paste:=xlPasteValues
    Range("P43:P53").Copy
    Range("Q43").PasteSpecial Paste:=xlPasteValues
    Range("R43:R53").Copy
    Range("S43").PasteSpecial Paste:=xlPasteValues
    Range("D833").Copy
    Range("P8").PasteSpecial Paste:=xlPasteValues
    Range("H8:H33").Copy
    Range("R8").PasteSpecial Paste:=xlPasteValues
    Range("K8:K33").Copy
    Range("M8").PasteSpecial Paste:=xlPasteValues
    Range("D4353").Copy
    Range("P43").PasteSpecial Paste:=xlPasteValues
    Range("H43:H53").Copy
    Range("R43").PasteSpecial Paste:=xlPasteValues
    Range("K43:K53").Copy
    Range("M43").PasteSpecial Paste:=xlPasteValues
    Range("K10").Copy
    Range("M10:S10, M14:S14, M18:S18, M22:S22, M26:S26, M30:S30, M34:S34, _
    M45:S45, M49:S49, M53:S53").PasteSpecial Paste:=xlPasteFormulas
    Range("K40").Copy
    Range("M40:S40").PasteSpecial Paste:=xlPasteFormulas
    Range("A3:A6").Select
    End Sub[/codebox]

    is there anything that can be trimmed?
    Regards
    Prasad

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    There are some patterns, but not so many that it's worth the trouble. I'd leave it as it is.

  6. #6
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='789491' date='18-Aug-2009 15:56']There are some patterns, but not so many that it's worth the trouble. I'd leave it as it is.[/quote]
    I think I should go with Hans's opinion & should leave it as it is.
    Regards
    Prasad

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='StuartR' post='789475' date='18-Aug-2009 08:10']One simple option would be to create a short Subroutine that does the copy for any specified range:............[/quote]

    A very useful approach, Thanks Stuart, I'm saving that in my Personal.xls for future reference.

  8. #8
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='prasad' post='789486' date='18-Aug-2009 15:09']Thanks StuartR
    Thanks Hans
    Implemented Hans's suggestion but not able to apply changes as suggested by StuartR. (due to my poor knowledge in VBA).

    Here is the entire code:

    [codebox]Sub changeweek()
    '
    ' changeweek

    '
    Dim varSheet
    For Each varSheet In Array("N1", "N2", "N3", "N4", "N5", "N6", "N8", "N9", "ANS", "ANTB")
    With Worksheets(varSheet)
    .Range("L4:L200").Copy
    .Range("S4").PasteSpecial Paste:=xlPasteValues
    .Tab.ColorIndex = xlColorIndexNone
    End With
    Next varSheet
    Sheets("Gr Summary").Select
    Range("D16:E22").Copy
    Range("E16").PasteSpecial Paste:=xlPasteValues
    Range("I16:J22").Copy
    Range("J16").PasteSpecial Paste:=xlPasteValues
    Range("N16:O22").Copy
    Range("O16").PasteSpecial Paste:=xlPasteValues
    Range("S16:T22").Copy
    Range("T16").PasteSpecial Paste:=xlPasteValues
    Range("D27:E33").Copy
    Range("E27").PasteSpecial Paste:=xlPasteValues
    Range("I27:J33").Copy
    Range("J27").PasteSpecial Paste:=xlPasteValues
    Range("N27:O33").Copy
    Range("O27").PasteSpecial Paste:=xlPasteValues
    Range("S27:T33").Copy
    Range("T27").PasteSpecial Paste:=xlPasteValues
    Range("D38:E44").Copy
    Range("E38").PasteSpecial Paste:=xlPasteValues
    Range("I38:J44").Copy
    Range("J38").PasteSpecial Paste:=xlPasteValues
    Range("N38:O44").Copy
    Range("O38").PasteSpecial Paste:=xlPasteValues
    Range("S38:T44").Copy
    Range("T38").PasteSpecial Paste:=xlPasteValues
    Range("B5").Select
    Sheets("BG3 ").Select
    Range("R1").Copy
    Range("T1").PasteSpecial Paste:=xlPasteValues
    Range("R1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[2]+7"
    Range("R1").Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("M8:M33").Copy
    Range("N8").PasteSpecial Paste:=xlPasteValues
    Range("P8:P33").Copy
    Range("Q8").PasteSpecial Paste:=xlPasteValues
    Range("R8:R33").Copy
    Range("S8").PasteSpecial Paste:=xlPasteValues
    Range("M43:M53").Copy
    Range("N43").PasteSpecial Paste:=xlPasteValues
    Range("P43:P53").Copy
    Range("Q43").PasteSpecial Paste:=xlPasteValues
    Range("R43:R53").Copy
    Range("S43").PasteSpecial Paste:=xlPasteValues
    Range("D833").Copy
    Range("P8").PasteSpecial Paste:=xlPasteValues
    Range("H8:H33").Copy
    Range("R8").PasteSpecial Paste:=xlPasteValues
    Range("K8:K33").Copy
    Range("M8").PasteSpecial Paste:=xlPasteValues
    Range("D4353").Copy
    Range("P43").PasteSpecial Paste:=xlPasteValues
    Range("H43:H53").Copy
    Range("R43").PasteSpecial Paste:=xlPasteValues
    Range("K43:K53").Copy
    Range("M43").PasteSpecial Paste:=xlPasteValues
    Range("K10").Copy
    Range("M10:S10, M14:S14, M18:S18, M22:S22, M26:S26, M30:S30, M34:S34, _
    M45:S45, M49:S49, M53:S53").PasteSpecial Paste:=xlPasteFormulas
    Range("K40").Copy
    Range("M40:S40").PasteSpecial Paste:=xlPasteFormulas
    Range("A3:A6").Select
    End Sub[/codebox]

    is there anything that can be trimmed?[/quote]

    After copy/paste the file, I used to make it usable for next week with above code. Apart from some other process, the code chage the tab color to none. This file is linked with other files saved in same folder with "vlookup" formula. On updation, I used to change color of tab to colorindex35 manually. Is it possible to amend code so that it can automatically change the tab color on updation?
    Regards
    Prasad

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    When would you like to change the tab color? In the macro that you quoted? If so, you only need to change the line

    .Tab.ColorIndex = xlColorIndexNone

    to

    .Tab.ColorIndex = 35

    Otherwise, please explain exactly when it should happen.

  10. #10
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='790252' date='22-Aug-2009 18:24']When would you like to change the tab color? In the macro that you quoted? If so, you only need to change the line

    .Tab.ColorIndex = xlColorIndexNone

    to

    .Tab.ColorIndex = 35

    Otherwise, please explain exactly when it should happen.[/quote]
    Let me explain the process:

    I have a folder contains 11 workbooks. 1 is consolidated wb, rest 10 are for different units. In consolidated sheet, there are 1 summary sheet & 10 other sheets for units. The name & formats of unit sheet and unit wbs are identical. When i save a new workbook say "N1" in said folder, it will update the sheet of "N1" in consolidated workbook. Then I manually change the tab color to mark it as updated. I want the macro to change the tab color "ON UPDATION" of sheet/s automatically.
    Regards
    Prasad

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Is "the macro" the one you describe in this topic? If so, I've given you the answer. If not, I don't understand.

  12. #12
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='790263' date='22-Aug-2009 20:20']Is "the macro" the one you describe in this topic? If so, I've given you the answer. If not, I don't understand.[/quote]
    This is an additional requirement. The ".Tab.ColorIndex = xlColorIndexNone" syntex change the tab color (presently the color index of tabs are 35, which I manually changed last week on updation) when I make a copy of wb for current week. This is first step. Now I have to update the wb for current week with data received from different locations. Here the requirement arise. I am looking for a code to change tab color at first instance of updation and any subsequent change in sheets should not affect the macro.
    Regards
    Prasad

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'm not sure this can be done. You might try the following code in the ThisWorkbook event of the workbook:

    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
      Sh.Tab.ColorIndex = 35
    End Sub
    But it might not work as intended and it might have undesired side effects.

  14. #14
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='790430' date='24-Aug-2009 11:29']I'm not sure this can be done. You might try the following code in the ThisWorkbook event of the workbook:

    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
      Sh.Tab.ColorIndex = 35
    End Sub
    But it might not work as intended and it might have undesired side effects.[/quote]
    To some extent, it is working. If I manually change the sheet, it works fine. But not working on updation through external links. Second, is it possible to restrict it to limited sheets.
    Regards
    Prasad

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can test the name of the sheet in the code.
    But indeed, the Worksheet_Change and Application_WorksheetChange events do not occur when an external reference changes.

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
  •