# How to Loop a Macro??

#### rodrigo_solares

##### New Member
Hello to all,

I've been using this primitive macro to bring some values from website:

Code:
``````Sub Tracker()
"URL;http://magpie.boise.itc.hp.com:8080/gip/fileStatus.jsp?transKey=GUS8301D&fileName=" & Right(Range("GUS8301!C3").Value, 16) _
, Destination:=Range("A1"))
.WebTables = "6,12"
.Refresh BackgroundQuery:=False
Range("GUS8301!A3").FormulaR1C1 = _
"=IF(VLOOKUP(""Start run."",Sheet1!C[1],1,FALSE)=""Start run."",OFFSET(Sheet1!C[1],MATCH(""Start Run."",Sheet1!C[1],0)-1,-1,1,1),FALSE)"
Range("GUS8301!A3").Copy
Range("GUS8301!A3").PasteSpecial Paste:=xlPasteValues
Range("GUS8301!B3").FormulaR1C1 = _
"=IF(VLOOKUP(""End run."",Sheet1!C,1,FALSE)=""End Run."",OFFSET(Sheet1!C,MATCH(""End Run."",Sheet1!C,0)-1,-1,1,1),FALSE)"
Range("GUS8301!B3").Copy
Range("GUS8301!B3").PasteSpecial Paste:=xlPasteValues
End With
End Sub``````

This macro is currently looking the website with data from cell C3 of GUS8301 Sheet... I want to be able to do this task for every cell with data in Column "C"...

Any help here would be greatly appreciated!!

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### ravishankar

##### Well-known Member
HI
try this

Code:
``````x= cells(rows.count,3).end(xlUp).row
for a = 3 to x
next a``````
and change "URL;http://magpie.boise.itc.hp.com:8080/gip/fileStatus.jsp?transKey=GUS8301D&fileName=" & Right(Range("GUS8301!C3").Value, 16) _
to "URL;http://magpie.boise.itc.hp.com:8080/gip/fileStatus.jsp?transKey=GUS8301D&fileName=" & Right(Range("GUS8301!C" & a).Value, 16) _
Run the macro
Ravi

#### rodrigo_solares

##### New Member
Hello Ravi,

The loop is working perfectly; however now i have two new problems:

1.- The macro is only copying the data to cell A3 & B3... not to all cells A & B... In fact, it is creating new sheets for every web query, but it is not copying the information from every new sheet to it's location...

2.- How can i add an instruction to the macro to delete the new sheet after it has copy & pasted the data that I'm looking; because after I've pasted the data, I don't longer need the sheet.

I'm attaching a copy of the Worksheet.. Hope this will help clarify a few things.
TRACKER PROJECT APRIL.xls
ABCDE
1StartRunEndRunFileRunTimeState
2GUS8301DBNAWECLD2.00705E+13H
339203.4839203.49GUS8301DBNAAKQ4K2.00705E+13C
4GUS8301DBN5TQWIL2.00704E+13C
5GUS8301DBN5I5OXM2.00704E+13C
6GUS8301DBN4QS12B2.00704E+13C
7GUS8301DBN4FMCXE2.00703E+13C
8GUS8301DBN40145J2.00703E+13C
9GUS8301DBN3OK0SD2.00703E+13C
10GUS8301DBN31ZVXH2.00703E+13C
11GUS8301DBN2AFH3M2.00702E+13C
12GUS8301DBN1NVFIG2.00702E+13C
GUS8301

As you can see.. the macro is only copying data from the first sheet to cells A3 & B3.. I want it to be able to copy data from every new sheet to it's corresponding Row.

Thanks in advance guys! and sorry for all the trouble!

#### rodrigo_solares

##### New Member
Ok,

I've solved the part where it's only copying data to the Cell A2 & B2 by adding the same character "& a" to every cell referece. However; The Offset formulas that I'm using are still refering to the Sheet1 instead of every new sheet.

I can only see two ways around this:

Either the formula needs to be changing to every new sheet or we enter into the macro an instruction to rename every new sheet to a specific name used by the formula (e.g. "TRACKER"). Therefore we would always look in the right Sheet.

#### rodrigo_solares

##### New Member
Hello again,

All these problems have been solved with this code:

Code:
``````Sub Tracker()
x = Cells(Rows.Count, 3).End(xlUp).Row
For a = 2 To x
"URL;http://magpie.boise.itc.hp.com:8080/gip/fileStatus.jsp?transKey=GUS8301D&fileName=" & Right(Range("GUS8301!C" & a).Value, 16) _
, Destination:=Range("A1"))
.WebTables = "6,12"
.Refresh BackgroundQuery:=False
Range("GUS8301!A" & a).FormulaR1C1 = _
"=IF(VLOOKUP(""Start run."",Tracker!C[1],1,FALSE)=""Start run."",OFFSET(Tracker!C[1],MATCH(""Start Run."",Tracker!C[1],0)-1,-1,1,1),FALSE)"
Range("GUS8301!A" & a).Copy
Range("GUS8301!A" & a).PasteSpecial Paste:=xlPasteValues
Range("GUS8301!B" & a).FormulaR1C1 = _
"=IF(VLOOKUP(""End run."",Tracker!C,1,FALSE)=""End Run."",OFFSET(Tracker!C,MATCH(""End Run."",Tracker!C,0)-1,-1,1,1),FALSE)"
Range("GUS8301!B" & a).Copy
Range("GUS8301!B" & a).PasteSpecial Paste:=xlPasteValues
End With
Sheets("Tracker").Delete
Next a
End Sub``````

Now I've arrived to stage 2 of the file. In the next worksheet all cells in column A & B have been correctly filled with the macro. The table beggining in column "C" it's a webquery, so I refresh it daily. Here's a image of the file filled by the macro...
TRACKER PROJECT JULY.xls
ABCDE
7038552.4138552.42GUS8301DBNEBHZKD2.00507E+13C
7138534.4838534.48GUS8301DBNDF1KCP2.00507E+13C
7238530.7938530.8GUS8301DBND4UMFJ2.00506E+13C
7338523.7138523.71GUS8301DBNCTDHTI2.00506E+13C
7438518.7138518.71GUS8301DBNCI1SNA2.00506E+13C
7538504.8838504.88GUS8301DBNBUCOBM2.00506E+13C
7638495.8838495.88GUS8301DBNBFHZVK2.00505E+13C
GUS8301

If I refresh the table the data now looks like this..
TRACKER PROJECT JULY.xls
ABCDE
7138534.4838534.48GUS8301DBNH3R3EI2.00509E+13C
7238530.7938530.8GUS8301DBNGLB03J2.00509E+13C
7338523.7138523.71GUS8301DBNG13QSO2.00508E+13C
7438518.7138518.71GUS8301DBNFPMMEJ2.00508E+13C
7538504.8838504.88GUS8301DBNEY4DZG2.00508E+13C
7638495.8838495.88GUS8301DBNEMZ5CI2.00507E+13C
77GUS8301DBNEBHZKD2.00507E+13C
78GUS8301DBNDF1KCP2.00507E+13C
79GUS8301DBND4UMFJ2.00506E+13C
80GUS8301DBNCTDHTI2.00506E+13C
81GUS8301DBNCI1SNA2.00506E+13C
82GUS8301DBNBUCOBM2.00506E+13C
83GUS8301DBNBFHZVK2.00505E+13C
GUS8301

I want the macro to Refresh the table, then copy the cells in A & B to the bottom of the table (to match column C) and then go look in the new webquery (the macro i already have) the data but "ONLY FOR THE CELLS IN COLUMN A that are blank i.e. the new ones".

Any help here would be greatly appreciated!!!

#### ravishankar

##### Well-known Member
Hi
you have used the hint I provided and have solved the rest of your problem. Good. I am unable to follow how you generate col A & B and refresh it and what relation it has with C etc.. can you elaborate?
Ravi

#### rodrigo_solares

##### New Member
Hi Ravi,

There is a webquery in the Worksheet in Cell C1... (That's the green cells). Columns A & B were filled with the macro you helped me make (The macro copy and pastes the values for all cells in Column A & B).

So far, so good

Let's say that the webquery has data from row 1 (Cell C1) up to row 70. When i refresh the webquery, new information gets refreshed and the data is now from row 1 to row 75. Therefore what was in cell C1 is now in cell C6.

Here is where the problem begins, because the data that was previously pasted into Column A & B doesn't move... And i have a problem because A1, B1 & C1 have a relationship, but since cell C1 has moved to cell C6 (because there is new data from the refresh) the data is no longer accurate. A1, B1 & C6 is the correct relation.

What i want the macro to do, is to move the data from Columns A & B to match the correct data from column C. For example, If the data in column C was moved by 5 rows below, i want to be able to copy column A & B five columns below.

After I made the movement, i want the macro to be able to run (the orignal macro where we populate the column A & B) only if the cell in column A is blank.

It's a little bit complicated... hope this explanation makes sense,.

Replies
4
Views
268
Replies
3
Views
100
Replies
9
Views
114
Replies
12
Views
249
Replies
5
Views
77

1,191,610
Messages
5,987,654
Members
440,104
Latest member
thigarette

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back