Help with macro to loop through cells and save a file

[The Ruff Report]

It's been awhile since I've created this macro and I'm having trouble.

I have a workbook "Sales" with a worksheet "Names" that has 100 names starting in cell A1. I want to copy the name in A1, paste into a new workbook (it updates a bunch of figures), and save that workbook under that name. I have the code for that fine.

I'm having trouble returning to the workbook "Sales" and the worksheet "Names" and performing the same operation for the name in A2, A3, A4.....until I reach A100

Any help is appreciated.

 

[The Ruff Report]

I actually found an older file with my macro:

Dim X As Integer
For X = 1 To 100

Sheets("Names").Select
Range("A" & X).Select
Selection.Copy

(rest of macro)

Next X

 

[Trebor76]

Hi there,

There is rarely a need to use select when working data. Try this where the values are assigned to an array and then the code loops through each item in it:

Option Explicit

Sub TheRuffReport()

    Dim arrMyData As Variant, varItem As Variant
    
    Application.ScreenUpdating = False
    
    arrMyData = ThisWorkbook.Sheets("Names").Range("A2:A100")
    
    For Each varItem In arrMyData
        'Your cuurent macro here where 'varItem' needs to go into cell A1 of the workbook where it will be saved as 'varItem'
    Next varItem
    
    Application.ScreenUpdating = False

End Sub

Regards,

Robert

 

[The Ruff Report]

Nice! Thank you for the feedback.

 

[The Ruff Report]

I'm not great at VBA but good enough to significantly improve some processes at work. I'm discovering how useless select is in all my macros. Duh!