loop

[philcud]

i have a loop statement where i want to loop through the values in the
range on the sheet "Lists" cells a2:a70 and set the value of "report
items location" cell a1 to this value, calculate, save as this value
and loop. i have so far:-

Sub loopthroughrangeandsave()
Dim nameofile As String
For Counter = 2 To 70
Set curCell = Worksheets("Lists").Cells(Counter, 3)
' Set curCell = Worksheets("Lists").Cells(Counter, 3)
curCell.Copy


Sheets("report items location").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


Application.Calculate

Set nameofile = curCell

activebook.SaveAs Filename:=nameofile
'SaveAs (Filename)


Next Counter


End Sub


but this fails on the line
activebook.SaveAs Filename:=nameofile
'SaveAs (Filename)
saying object required?

 

[Per Jessen]

Hi

You don't use Set when you assign a value to a string variable, Just
NameOfile=curCell.value

Change activebook to ActiveWorkbook.

It can also be done like this:

Sub loopthroughrangeandsave()
Dim curCell As Range
Dim CopyToCell As Range
Dim NameOfile As String

Set CopyToCell = Sheets("report items location").Range("A1")
Set curCell = Worksheets("Lists").Cells(2, 3)

For off = 0 To 68
curCell.Offset(off, 0).Copy
CopyToCell.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.Calculate
NameOfile = curCell.Value
ActiveWorkbook.SaveAs Filename:=NameOfile
Next
End Sub

Hopes this helps.

 

[philcud]

Hi Per - the amended code works great, but the filename doesn't chenge
on each loop?