Loop problem - combining code

[philobr]

I have two seperate pieces of code that work, but I need to
get them to loop through a list and use the list in saving as filename.
But I've no idea how to use the filename or create the loop through the list
so Im here looking for a kind soul willing to help.

Sheet4 B7 is the start of a named range "ListItems" I have code to copy
to where I want, but dont know how to add a loop to go through the list

Sheets("Sheet4").Range("B7").Copy Sheets("Sheet1").Range("B7")

So after each copy I need to do run the next bit of code.
If I put the two together they are ok, but I have to keep changing the file name
every time I run it.
I dont know how to loop through the list or use B7 to add to the filename

Sub SAVEASHTML()
   Sheets("Sheet1").Select
    With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, "C:\test\List1_Page.htm", _
        "Sheet1", "", xlHtmlStatic, "Book2", "")
        .Publish (True)
        .AutoRepublish = False
    End With
   Sheets("Sheet2").Select
    With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, "C:\test\List1_Page2.htm", _
        "Sheet2", "", xlHtmlStatic, "Book2", "")
        .Publish (True)
        .AutoRepublish = False
    End WithEnd Sub

Anyone able to help??

 

[Andrew Poulsom]

Can you give examples of what's in ListItems, where the contents should be copied and what the file name should be?

 

[phantom1975]

If you are trying to loop through each cell in "ListItems", you need to create a For Each....Next loop. Assuming you are a newbie to loops, I'll give you a simple loop to wrap your brain around.

Sub Simple_Loop
Dim Counter as Long

For Counter = 1 to 10
   Msgbox "The counter number is " & Counter
Next Counter

End Sub

The first thing I did was declare a variable for the kind of data I would iterate through. In this case, I am iterating through numbers (declared as type Long.) The code basically states that counter will start with 1 and iterate by 1 (implied) until it reaches 10. If I wanted it to iterate by 2 instead, I would have changed the code to this:

For Counter = 1 to 10 Step 2

Sometimes you need to iterate backwards:

For Counter = 10 to 1 Step -1

When creating a loop for an object, you use the For Each.....Next loop. Here's an example of what you need:

Dim ThisCell as Range

For Each ThisCell in Range("ListItems")

     ActiveWorkbook.SaveAs ThisCell.Value & ".xls"

Next ThisCell

I hope this points you in the right direction.

 

[philobr]

Can you give examples of what's in ListItems, where the contents should be copied and what the file name should be?

The list items are just plain text in a column (A:A) (A10) on Sheet1,
can vary between 10 and 80 rows.
I've a dynamic named range and a validation list Sheet2 (A3) references these.
When you select an item from the validation list a macro copies the data to the right
and pastes in Sheet2 which is then used to populate charts in Sheets3, 4 and 5.

I tried recording to select the validation list, but vb just selects the cell.
I can write the code to copy from Sheet1 and pasting to Sheet2

Sheets("Sheet1").Range("A10").Copy Sheets("Sheet2").Range("A3")

But then Im stuck (keep getting stuck, such a pain.)
I need to go down through the list and copy each item.

But I have an extra bit

I recorded a macro to save the three sheets as html in a folder
(see earlier code and apologies I know its a horrible nightmare but it works).
Every time I choose the next item in the list I need to save 3 sheets (3, 4, 5)
as the sheet name plus the order of the list item.
I can add an extra column and put 1-80 or something in B:B on sheet1 for a
reference thats not a big deal.
So you'd get -
Orange 1
Apple 2

The saved file names would be 1_sheet3, 1_sheet4, 1_sheet5, then choose the
next item in the list and get 2_sheet3, 2_sheet4, 2_sheet5 and so on

The code to save (back above) just saves as one file name.
So what Im doing at the minute is viewing the code.
Selecting the next item in the list, saving the files.
Change the file name in the code, select next item, save the files.
So you can see how much of a pain this is.

 

[philobr]

Thanks Phantom for the advice on the loops, I have another (simpler) problem Im going to try to use the help you gave on. Thanks.

 

[Andrew Poulsom]

Sorry, I am still confused here. First you had:

Sheets("Sheet4").Range("B7").Copy Sheets("Sheet1").Range("B7")

and now it's:

Sheets("Sheet1").Range("A10").Copy Sheets("Sheet2").Range("A3")

It might help if you posted some sample data and explained what range you wanted to loop around, where it would be copied and what cell would determine the file names.

 

[philobr]

Apologies Andrew, I was changing the sheet to try to get things working.

All the details are in the Mar 10th, 2009 05:26 PM post

Re: Loop problem - combining code

--------------------------------------------------------------------------------

Quote:
Originally Posted by Andrew Poulsom
Can you give examples of what's in ListItems, where the contents should be copied and what the file name should be?

The list items are just plain text in a column (A:A) (A10) on Sheet1,
can vary between 10 and 80 rows.
I've a dynamic named range and a validation list Sheet2 (A3) references these.
When you select an item from the validation list a macro copies the data to the right
and pastes in Sheet2 which is then used to populate charts in Sheets3, 4 and 5.

I tried recording to select the validation list, but vb just selects the cell.
I can write the code to copy from Sheet1 and pasting to Sheet2

Code:
Sheets("Sheet1").Range("A10").Copy Sheets("Sheet2").Range("A3")But then Im stuck (keep getting stuck, such a pain.)
I need to go down through the list and copy each item.

But I have an extra bit

I recorded a macro to save the three sheets as html in a folder
(see earlier code and apologies I know its a horrible nightmare but it works).
Every time I choose the next item in the list I need to save 3 sheets (3, 4, 5)
as the sheet name plus the order of the list item.
I can add an extra column and put 1-80 or something in B:B on sheet1 for a
reference thats not a big deal.
So you'd get -
Orange 1
Apple 2

The saved file names would be 1_sheet3, 1_sheet4, 1_sheet5, then choose the
next item in the list and get 2_sheet3, 2_sheet4, 2_sheet5 and so on

The code to save (back above) just saves as one file name.
So what Im doing at the minute is viewing the code.
Selecting the next item in the list, saving the files.

 

[philobr]

Bumping to try to get some interest.
Really killing me, took 2 hrs the other day to do this manually
so Im fairly desperate for something that would probably take 2 mins.