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?
|