Macro loop help

wilkisa

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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

hayden

Board Regular
Joined
Sep 23, 2005
Messages
160
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
654
Office Version
  1. 365
  2. 2016
  3. 2013
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,171,968
Messages
5,878,537
Members
433,352
Latest member
horna

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