VBA For Next Loop

[MattNewby]

Hi,

I am trying to update a report with a list of cost centres and copy each report created to a new tab.

The sheets in my workbook are called:

• CC List
• CC REP

The steps I am trying to code into a for next loop are as follows:

• update a specific cell (criteria key for a sum if) on CC REP sheet with a list of cost centre codes from another (one code at a time).
• Copy the report that the updated cell drives into a new tab

Code for the For Next Loop

Dim Col As Integer

For Row = 1 To 9

Set i = Sheets("CCList").Cells(Row, 1)
Sheets("CCREP").Range("A8").Value = i

Call Copysheet

Next Row

Please note the sample cost centre codes are in sheet CCREP, cells A1 to A9


Code for Copying to a New Tab

Sub Copysheet()

Dim MyDate As String
Dim CC As Range



MyDate = Format(Now, "mm-ss")

Set CC = Sheets("CCLIST").Range("A8")


Sheets("CCREP").Copy After:=Sheets("CCREP")
Sheets("CCREP (2)").Name = CC & " | " & MyDate


With Sheets(CC & " | " & MyDate)

.Unprotect
.Cells.Copy
.Cells.PasteSpecial xlValues

End With

Range("A8").Select
SendKeys "{ESC}", True
Sheets("CCREP").Activate

End Sub


This bit of code works just fine


Overview

The issue that I have is that both parts work until I call the copysheet module and then the vba crashes.

I don't understant the error???

What i was expecting from this code was the following:

• CC codes passed to CCREP sheet in cell A8
• The sheet copied to a new tab as paste special values, and tab renamed as the cost centre code
• The next cycle of the loop to continue

This is not the case and if anyone can solve this problem, explain why it will not work, or point me in the right direction I will be very greatful.

Thank you in advance.

Matt

 

[dUBBINS]

I am not a pro by any means. And maybe you are doing something I simply do not understand. But it seems wrong that you are telling Excel to copy a sheet, after that same sheet.

Sheets("CCREP").Copy After:=Sheets("CCREP")

Plus, can you copy a range instead of the entire sheet? Even if the range changes there are ways to detect that. But copying the whole sheet and pasting it seems like a large amount of data transferring; although, perhaps VBA knows not to copy millions of blank cells. I do not know for sure. And if it is only doing it once, any lag is probably not a problem.

But what is the error, and what line is it highlighting?

 

[dUBBINS]

I misread where you were having a problem. But I am still curious how that works. So I am going to leave it and hope someone explains.

Ah, I think your error is happening just before it calls Copysheet. Try adding:

Dim Col As Integer

For Row = 1 To 9

Sheets("CCList").Activate
Set i = Sheets("CCList").Cells(Row, 1)
Sheets("CCREP").Activate
Sheets("CCREP").Range("A8").Value = i

Call Copysheet

Next Row

 

[My Aswer Is This]

Copying a sheet is no problem at all.

Just use a piece of code like this:

Sub Copy_Sheet()
Sheets("George").Copy after:=Sheets("George")
ActiveSheet.Name = "Bob"
End Sub

 

[MattNewby]

Thanks, but what i am trying to do is more complicated than that.

Matt

 

[MattNewby]

Thanks,

I think the issue is that I need to pause the loop and allow each step to recalculate.
Not100% sure, but thanks for your input anyhow.

Matt

 

[dUBBINS]

Matt, did you try adding the lines I suggested? Not 100% if that is the problem, but usually you must activate a sheet to affect it.

Either way, I do not believe that you need to pause the loop at all.

Have you stepped through your code? (click on the left side just off where you type the code lines on the line that starts the loop, FOR, then after starting the macro, press F8 to step through each line. This makes it run as slow as you press the button. The code cannot run any slower than that, so you will see that speed is not the issue; and will better see where it is going wrong)

 

[MattNewby]

Hi All,

Thanks for your input. I am gonig to park this as unresolved and fins another way of doing it.

Probably me not explaing/ understanding the problem correctly.

Matt