Macro loop help

[wilkisa]

I have recorded a macro that I want to put into a loop that will process exactly 25 times and then stop but I don't know how to do it. I have been checking my books and have just confused myself. Also, the first execution always starts in N32 and the next will be 8 rows down each time (N40, N48, N56, etc.)

I also need this macro to open the target workbook and match the active sheet tab name to the tab name in the target workbook. In other words, if I am in sheet 18 in the source book, it has to select sheet 18 in the target. Each day when the macro is executed, the sheet number will change.

My recorded code is as follows:

Sub K81Test1()
' Workbooks.Open Filename:="Septembertest1.xls", _
UpdateLinks:=0
ActiveWindow.SmallScroll Down:=-15
Windows("Lab SHEET Sep 2006test1.xls").Activate
Range("N32").Select
Selection.Copy
Windows("Septembertest1.xls").Activate
Range("C7").Select
Sheets("17").Select
ActiveWindow.SmallScroll Down:=-12
Range("C7").Select
ActiveSheet.Paste Link:=True
Windows("Lab SHEET Sep 2006test1.xls").Activate
Range("N40").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Septembertest1.xls").Activate
Range("C8").Select
ActiveSheet.Paste Link:=True
Windows("Lab SHEET Sep 2006test1.xls").Activate
End Sub

I only recorded 2 of the 25 steps, just for the sake of time, but I know a loop can pick up the rest without my having to record it first.

Can someone please help with the 2 issues I outlined. I would be most grateful.

 

[hayden]

I can help with the loop, but not the sheet verification.

Sub K81Test1()
dim j as integer
' Workbooks.Open Filename:="Septembertest1.xls", _
UpdateLinks:=0
Windows("Lab SHEET Sep 2006test1.xls").Activate
j = 32
for i = 7 to 32
Cells(j,14).Select
Selection.Copy
Windows("Septembertest1.xls").Activate
Cells(i,3).select
ActiveSheet.Paste Link:=True
Windows("Lab SHEET Sep 2006test1.xls").Activate
j = j + 8
next
End Sub

A couple of things about this. i is set to 7 because you were pasting in cell C7. J is set to 32 to start since that is where you always want to start and is incremented by 8 ever time the loop completes. i will be incremented by 1 via the next line.

Try it out. I didn't test it but i think it should work. I would step through it using f8. Good luck.

Hayden

 

[wilkisa]

Thank you, Hayden, I was able to modify it slightly and it works great!!! I will work on the sheet tab matching issue but this is huge for me.

Thank you so much, again
Shirlene