Keep Format while using an Array

imback2nite

imback2nite

Board Regular
Joined
Oct 30, 2004
Messages
203
Office Version
  1. 2003 or older
Platform
  1. Windows
Is it possible to use the code below and keep the formatting in the affected cells? I’m using this code but it goes on for thirty pages. The Array works but the formatting is totally gone. I need the Bold and Font to stay the same as well as the cell colors. Is it possible? Or is there a better way entirely? I'm using Excel 2003. Yes, ancient. :(
Rich (BB code): 
For Each iselect In Sheets(Array("Emp1", "Emp2",”Emp3”,”Emp4”, “Emp5” to  "Emp30"))
Sheets(iselect.Name).Activate
Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Next iselect
This code does what I want but it's huge!
Rich (BB code): 
Sheets("Emp1").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp1").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp1").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp2").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp2").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp2").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp3").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp3").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp3").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp4").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp4").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp4").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp5").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp5").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp5").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp6").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp6").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp6").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp7").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp7").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp7").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp8").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp8").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp8").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp9").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp9").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp9").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp10").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp10").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp10").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp11").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp11").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp11").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp12").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp12").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp12").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp13").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp13").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp13").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp14").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp14").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp14").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp15").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp15").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp15").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp16").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp16").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp16").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp17").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp17").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp17").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp18").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp18").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp18").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp19").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp19").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp19").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp20").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp20").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp20").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp21").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp21").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp21").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp22").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp22").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp22").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp23").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp23").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp23").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp24").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp24").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp24").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp25").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp25").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp25").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp26").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp26").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp26").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp27").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp27").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp27").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp28").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp28").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp28").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp29").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp29").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp29").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
Sheets("Emp30").Range("P8:P9") = Sheets("Tally Sheet").Range("Z15:Z16").Value
Sheets("Emp30").Range("Y3:Y9") = Sheets("Tally Sheet").Range("Z17:Z23").Value
Sheets("Emp30").Range("AF3:AF9") = Sheets("Tally Sheet").Range("Z24:Z30").Value
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Try the following as a replacement for your 'Huge code' section:
VBA Code: 
Sub ShortenedCode()
'
    Dim TallySheetRange1    As Range, TallySheetRange2  As Range, TallySheetRange3  As Range
    Dim ws                  As Worksheet
'
    With Sheets("Tally Sheet")
        Set TallySheetRange1 = .Range("Z15:Z16")
        Set TallySheetRange2 = .Range("Z17:Z23")
        Set TallySheetRange3 = .Range("Z24:Z30")
    End With
'
    For Each ws In Worksheets                                                                                           ' Loop through all worksheets in the workbook
        Select Case ws.Name
            Case Is = "SheetNameToExclude", "Tally Sheet"                                                               '       <--- Set this to the sheet names to exclude
'               Skip these sheets
            Case Else                                                                                                   '       All other sheets ...
                ws.Range("P8:P9") = TallySheetRange1.Value
                ws.Range("Y3:Y9") = TallySheetRange2.Value
                ws.Range("AF3:AF9") = TallySheetRange3.Value
        End Select
    Next                                                                                                                ' Loop back
End Sub
 
Upvote 0
Solution
johnnyL said:
Try the following as a replacement for your 'Huge code' section:
VBA Code: 
Sub ShortenedCode()
'
    Dim TallySheetRange1    As Range, TallySheetRange2  As Range, TallySheetRange3  As Range
    Dim ws                  As Worksheet
'
    With Sheets("Tally Sheet")
        Set TallySheetRange1 = .Range("Z15:Z16")
        Set TallySheetRange2 = .Range("Z17:Z23")
        Set TallySheetRange3 = .Range("Z24:Z30")
    End With
'
    For Each ws In Worksheets                                                                                           ' Loop through all worksheets in the workbook
        Select Case ws.Name
            Case Is = "SheetNameToExclude", "Tally Sheet"                                                               '       <--- Set this to the sheet names to exclude
'               Skip these sheets
            Case Else                                                                                                   '       All other sheets ...
                ws.Range("P8:P9") = TallySheetRange1.Value
                ws.Range("Y3:Y9") = TallySheetRange2.Value
                ws.Range("AF3:AF9") = TallySheetRange3.Value
        End Select
    Next                                                                                                                ' Loop back
End Sub
Click to expand...
Genius! That worked perfectly!! Thank you so much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

imback2nite
  • Solved
Help shorten loooong code?
Replies
2
Views
368
imback2nite
imback2nite
imback2nite
  • Solved
Worksheet Change on multiple sheets
2 3
Replies
21
Views
1K
My Aswer Is This
My Aswer Is This
P
  • Solved
Keep W-L of card game played over time
Replies
6
Views
424
Preacherman771
P
C
  • Solved
Referencing accrued leave value for last last months last working day
Replies
5
Views
333
Peter_SSs
Peter_SSs
U
  • Question
Chart creation - best presentation?
Replies
2
Views
318
Fluff
Fluff

Forum statistics

Threads
1,215,242
Messages
6,123,827
Members
449,127
Latest member
Cyko

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top