(Solved) – Updating Multiple Workbooks based on 1 Cells Value (VBA)

  • by
(solved)-–-updating-multiple-workbooks-based-on-1-cells-value-(vba)

I’m new to VBA, and I’m trying to optimize some code. I currently have a few workbooks that are being updated based on a single cells value, and then copied to a new workbook. Is there a way of running a For loop so that I don’t have to add a new block of code for each unique value?

'
Range("Account") = "='A1 List'!R[1]C[0]"
Calculate
ActiveWorkbook.RefreshAll
Columns("D:F").Select
Selection.ColumnWidth = 11
    Cells.Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
ThisWorkbook.SaveCopyAs Filename:=XPath & "" & Range("Account") & ".xlsm"'
  Range("Account") = "='A1 List'!R[2]C[0]"
Calculate
ActiveWorkbook.RefreshAll
Columns("D:F").Select
Selection.ColumnWidth = 11
    Cells.Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
ThisWorkbook.SaveCopyAs Filename:=XPath & "" & Range("Account") & ".xlsm"

TIA

Leave a Reply

Your email address will not be published. Required fields are marked *