Macro loop help

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
651
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2007
Platform
  1. Windows
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

hayden

Board Regular
Joined
Sep 23, 2005
Messages
159
I can help with the loop, but not the sheet verification.

Code:
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

Well-known Member
Joined
Apr 7, 2002
Messages
651
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2007
Platform
  1. Windows
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

:biggrin: :biggrin: :biggrin:
 

Forum statistics

Threads
1,136,353
Messages
5,675,283
Members
419,559
Latest member
BraytonM

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top