loop
 

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?

loop
 

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.

---
Per

On 28 Apr., 15:12, philcud wrote:
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?

loop
 

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

On 28 Apr, 15:40, Per Jessen wrote:
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.

---
Per

On 28 Apr., 15:12, philcud wrote:

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?