Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
returning back to loop check condition without completing the loop | Excel Programming | |||
Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming |