0

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

riaden14
  • 13
  • 3
  • 2
    As you are new to VBA I'd recomment to do some reading for example on [for loops](https://excelmacromastery.com/vba-for-loop/) or you go for [Power Query](https://support.office.com/en-us/article/introduction-to-microsoft-power-query-for-excel-6e92e2f4-2079-4e1f-bad5-89f6269cd605?ui=en-US&rs=en-US&ad=US) which might also be a solution for your problem. – Storax Dec 31 '19 at 16:57
  • Reading up on [how to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code will also be useful – cybernetic.nomad Dec 31 '19 at 16:59

0 Answers0