Hard code non-contiguous cells on multiple sheets

[choop]

Hello,

Each month I need to update our monthly budget. To do this, I need to hard code the old budget and then only update the most current month. I don't want to hard code summations of total expenses/margins/etc, only the actual financial data that goes into those summations.

Here is my scenario

• 5 tabs (call them A, B, C, D, E)
• Each tab is annual financials by month in the same format (each tab represents a different location in a company)
• There is a column for financials and % of sales for each month, then a quarterly summary at the end of each quarter.
• Each row is either a line of financial data, or a summation of lines above it

​

I need to loop through each tab (A-E), hardcode all cells for financial data (see range below for one month), but not the summations....​

​

In other words, I have non contiguous cells in 12 non contiguous columns on 5 tabs that I need to hardcode all at the same time.​

​

I tried this code (code for one column's worth of data):​

Range("H7,H11:H16,H18:H19,H25:H30,H32:H41,H43:H47,H55:H71,H75:H79").Select​

With ActiveSheet.Selection​

.Value = .Value​

End With​

This gives me an error; I tried range instead of selection and it kind of hung...

Would love any help!
Thanks!

 

[Joe4]

Try this instead:

    Dim cell As Range

    Application.ScreenUpdating = False

    For Each cell In Range("H7,H11:H16,H18:H19,H25:H30,H32:H41,H43:H47,H55:H71,H75:H79")
        cell.Value = cell.Value
    Next cell

    Application.ScreenUpdating = True

 

[choop]

Try this instead:

    Dim cell As Range

    Application.ScreenUpdating = False

    For Each cell In Range("H7,H11:H16,H18:H19,H25:H30,H32:H41,H43:H47,H55:H71,H75:H79")
        cell.Value = cell.Value
    Next cell

    Application.ScreenUpdating = True

This should be perfect, thank you! If you are so inclined, how can I choose which columns I want to hard code dynamically and loop through them? I figured I could set up variables for each month as follows, but then get a little lost on how to choose them through a message box and two put the relevant ones into an array to use a for loop? I also need to add a For Loop to loop through the relevant tabs, but I figure I can apply what you show me here to that question. Thank you!!!

Dim January As Range
Dim February As Range
Dim March As Range
Dim April As Range
Dim May As Range
Dim June As Range
Dim July As Range
Dim August As Range
Dim September As Range
Dim October As Range
Dim November As Range
Dim December As Range

RELEVANT_MONTHS = {January, February March} <-- would like to get this from a user input

Set January = Range("D7,D11:D16,D18:D19,D25:D30,D32:D41,D43:D47,D55:D71,D75:D79")
Set February = Range("F7,F11:F16,F18:F19,F25:F30,F32:F41,F43:F47,F55:F71,F75:F79")
Set March = Range("H7,H11:H16,H18:H19,H25:H30,H32:H41,H43:H47,H55:H71,H75:H79")
Set April = Range("L7,L11:L16,L18:L19,L25:L30,L32:L41,L43:L47,L55:L71,L75:L79")
Set May = Range("N7,N11:N16,N18:N19,N25:N30,N32:N41,N43:N47,N55:N71,N75:N79")
Set June = Range("P7,P11:P16,P18:P19,P25:P30,P32:P41,P43:P47,P55:P71,P75:P79")
Set July = Range("T7,T11:T16,T18:T19,T25:T30,T32:T41,T43:T47,T55:T71,T75:T79")
Set August = Range("V7,V11:V16,V18:V19,V25:V30,V32:V41,V43:V47,V55:V71,V75:V79")
Set September = Range("X7,X11:X16,X18:X19,X25:X30,X32:X41,X43:X47,X55:X71,X75:X79")
Set October = Range("AB7,AB11:AB16,AB18:AB19,AB25:AB30,AB32:AB41,AB43:AB47,AB55:AB71,AB75:AB79")
Set November = Range("AD7,AD11:AD16,AD18:AD19,AD25:AD30,AD32:AD41,AD43:AD47,AD55:AD71,AD75:AD79")
Set December = Range("AF7,AF11:AF16,AF18:AF19,AF25:AF30,AF32:AF41,AF43:AF47,AF55:AF71,AF75:AF79")

For Each Range in Relevant_Months
For Each cell In Relevant_Months.??

cell.Value = cell.Value​

Next cell​

Next Range

 

[DanteAmor]

Hi @choop ,

I see a pattern in your columns like this:

how can I choose which columns I want to hard code dynamically and loop through them?

We can add an InputBox like this:

Relevant_Months = InputBox("Write the months, to January:1, February:2" & vbCr & "E.g: 1,2,3,8,12", "Relevant_Months")

-------------------------- ----- --

I also need to add a For Loop to loop through the relevant tabs

With a cycle for the sheets:

  arr_sheets = Array("A", "B", "C", "D", "E")
  For Each arr In arr_sheets

-------------------------- ----- --
So, you can use the following to loop through the relevant months, for all sheets:

Sub multiple_Values()
  Dim sh As Worksheet
  Dim rng As Range, c As Range
  Dim i As Long, n As Long
  Dim Relevant_Months As Variant, m As Variant, arr_sheets As Variant, arr As Variant
 
  Relevant_Months = InputBox("Write the months, to January:1, February:2" & vbCr & "E.g: 1,2,3,8,12", "Relevant_Months")
  If Relevant_Months = "" Then
    Exit Sub
  End If
 
  'Verify that the months entered are actually months
  For Each m In Split(Relevant_Months, ",")
    If Not (Val(m) >= 1 And Val(m) <= 12) Then
      MsgBox "Please, enter each month separated by commas. E.g: 1,2,3,8,12"
      Exit Sub
    End If
  Next
 
  '5 tabs (call them A, B, C, D, E)
  arr_sheets = Array("A", "B", "C", "D", "E")
  For Each arr In arr_sheets
    Set sh = Sheets(arr)
   
    'Set only the initial range
    Set rng = sh.Range("D7,D11:D16,D18:D19,D25:D30,D32:D41,D43:D47,D55:D71,D75:D79")
   
    For Each m In Split(Relevant_Months, ",")
      i = Val(Trim(m))
      Select Case i
        Case 1:  n = 0
        Case 2:  n = 2
        Case 3:  n = 4
        Case 4:  n = 8
        Case 5:  n = 10
        Case 6:  n = 12
        Case 7:  n = 16
        Case 8:  n = 18
        Case 9:  n = 20
        Case 10: n = 24
        Case 11: n = 26
        Case 12: n = 28
      End Select
     
      For Each c In rng.Offset(0, n)
        c.Value = c.Value
      Next c
     
    Next m    'next month
  Next arr    'next sheet
End Sub

 

[choop]

Wow - I'm blown away, what an elegant solution! Thank you!

 

[DanteAmor]

Hi @choop ,
Note: You should mark as an answer the post that actually answered your question. Unless your own answer is the solution.