Macro (Excel 2003)

Topic

New Reply

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]

Reply | Quote

Replies

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.

Reply | Quote

One simple option would be to create a short Subroutine that does the copy for any specified range:…………

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

Reply | Quote

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

Reply | Quote

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(“D8:D33”).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(“D43:D53”).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?

Reply | Quote

There are some patterns, but not so many that it’s worth the trouble. I’d leave it as it is.

Reply | Quote

There are some patterns, but not so many that it’s worth the trouble. I’d leave it as it is.

I think I should go with Hans’s opinion & should leave it as it is.

Reply | Quote

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(“D8:D33”).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(“D43:D53”).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?

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?

Reply | Quote

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.

Reply | Quote

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.

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.

Reply | Quote

Is “the macro” the one you describe in this topic? If so, I’ve given you the answer. If not, I don’t understand.

Reply | Quote

Is “the macro” the one you describe in this topic? If so, I’ve given you the answer. If not, I don’t understand.

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.

Reply | Quote

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.

Reply | Quote

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.

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.

Reply | Quote

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.

Reply | Quote

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.

The entire wb updated through external references and manual intervene is rare. It is better to drop the idea and do it manually to avoid any undesired side effects as advised.

Thanks Hans for taking time.

Reply | 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.

This is with reference to an earlier post on similer topic.

Here I want a worksheet_calculate event to change the tab color on updation through external links/formulas.

Reply | Quote

So what exactly is the problem? You’ve been given all the ingredients you need.

Reply | Quote

So what exactly is the problem? You’ve been given all the ingredients you need.

Thanks Hans for your kind support but I am not that efficient, particular in VBA/writing Codes. I am still struggling with it but no results so far. I dont know what procedures should be written Or even where to insert the code, in individual sheets or in this workbook or create a new module. Pl help.

Reply | Quote

You could put the following code in the ThisWorkbook module:

Code:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
  Sh.Tab.ColorIndex = 35
End Sub

Reply | Quote

You could put the following code in the ThisWorkbook module:

[codebox]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[/codebox]

Putting the code in the ThisWorkbook module changes the tab color of all sheets.

Reply | Quote

Code:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
  Select Case Sh.Name
	Case "N1", "N2", "N3", "N4", "N5", "N6", "N8", "N9", "ANS", "ANTB"
	  Sh.Tab.ColorIndex = 35
  End Select
End Sub

Reply | Quote

Thank you very much Hans for keeping the hopes alive.

Reply | Quote