Keep Format while using an Array

imback2nite

imback2nite

Board Regular
Joined
Oct 30, 2004
Messages
211
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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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

V
  • Solved
Average Function
Replies
8
Views
395
Peter_SSs
Peter_SSs
H
  • Question Question
Calculate stake based on cumulative bank
Replies
0
Views
101
honkin
H
imback2nite
  • Solved
Help shorten loooong code?
Replies
2
Views
504
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
Lookup based on multiple criteria
2 3
Replies
22
Views
934
paulkc
P

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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