Excel copying wrong sheet in VBA loop

Hi there eveybody,

I am having a problem with a loop in excel, I specify it to copy one sheet but it seems to copy another, however this can be mitagated by calling Workbook.Save on each iteration, but I don't really want to do this as its going to be used as a template. Maybe I am referencing something wrong? Thanks.
Sub GenSheets()

Dim rng    As Range
    Dim cl     As Range
    With Sheets("Quotation")
        Set rng = .Range(.Cells(6, 1), .Cells(6, 1).End(xlDown))
        For Each cl In rng
            strname = "OL " & cl.Value
            'Blank is the name of my template worksheet
            Sheets("Blank").Copy After:=Sheets(Sheets.Count)
            Set sh = Sheets(Sheets.Count)
            sh.Name = strname
            
            sh.Visible = True 'xlHidden
            sh.Cells(3, 2) = "=Quotation!$F$" & cl.Value + 5
            sh.Cells(3, 3) = "=Quotation!$B$" & cl.Value + 5
            sh.Cells(3, 4) = "=Quotation!$C$" & cl.Value + 5
            sh.Cells(3, 5) = "=Quotation!$D$" & cl.Value + 5
            sh.Cells(3, 7) = "=Quotation!$E$" & cl.Value + 5
            Set q = Sheets("Quotation")
            q.Cells(cl.Value + 5, 35) = "='" & strname & "'!$J$31"
        Next cl
    End With
    
    End Sub

Open in new window

LVL 2
wellsoAsked:
Who is Participating?
 
Chris BottomleyConnect With a Mentor Software Quality Lead EngineerCommented:
The following also seems ok ... I save it as the first sheet then move it and all seems well!

Chris
Sub GenSheets()

Dim rng    As Range
    Dim cl     As Range
    With Sheets("Quotation")
        Set rng = .Range(.Cells(6, 1), .Cells(6, 1).End(xlDown))
        For Each cl In rng
            strname = "OL " & cl.Value
            'Blank is the name of my template worksheet
            Sheets("Blank").Copy before:=Sheets(1)
            Set Sh = Sheets(1)
            Sh.Name = strname
            Sh.Move after:=Sheets(Sheets.Count)
            Sh.Visible = True 'xlHidden
            Sh.Cells(3, 2) = "=Quotation!$F$" & cl.Value + 5
            Sh.Cells(3, 3) = "=Quotation!$B$" & cl.Value + 5
            Sh.Cells(3, 4) = "=Quotation!$C$" & cl.Value + 5
            Sh.Cells(3, 5) = "=Quotation!$D$" & cl.Value + 5
            Sh.Cells(3, 7) = "=Quotation!$E$" & cl.Value + 5
            Set q = Sheets("Quotation")
            q.Cells(cl.Value + 5, 35) = "='" & strname & "'!$J$31"
        Next cl
    End With
    
    End Sub

Open in new window

0
 
StephenJRCommented:
OK so which sheet do you want to copy and which sheet is it actually copying?
0
 
wellsoAuthor Commented:
It keeps copying a dialog sheet for some reason, or the last sheet in the book if I delete that sheet
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
wellsoAuthor Commented:
The sheet I wanted to copy was called 'Blank'
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Sorry to be negative but are you sure you are running the code you supplied ... there is no reason for it to copy anything other than 'blank' that I can see.

Perhaps a workbook demonstrating th eproblem will help - if it has nothing sesitive therein of course

Chris
0
 
KnutsonBMCommented:
do you have another workbook open with a sheet called "Blank"  if you do, then activating the correct workbook should work or doing something like workbooks("abcdefg").Sheets("Blank").Copy


Brandon
0
 
StephenJRCommented:
Your code does copy 'Blank'. Can you post a workbook?
0
 
StephenJRCommented:
Sorry chaps, hadn't seen your replies.
0
 
KnutsonBMCommented:
Workbooks("abc123.xls").Activate
Sheets("Blank").Copy After:=Workbooks("efg456.xls").Sheets(Sheets.Count)

replace abc123.xls with the name of your workbook you are copying from and replace efg456.xls with the destination workbook
0
 
wellsoAuthor Commented:
Heres the file, sorry for the delay
Original-Draft---Copy--3-.xls
0
 
KnutsonBMCommented:
the sheet "Blank" was hidden.............did you want it left that way?  are you just trying to 'Move' the sheet to the end or do you want a copy of it moved.......?
0
 
wellsoAuthor Commented:
Its hidden but I want to unhide the copies as they are created please
0
 
KnutsonBMCommented:
so here is what did and your code seems to work fine.....unhid "Blank" and deleted the OL1 that was created (don't know where it came from, it created a new "Blank (x)" sheet, then created OL1-OL15.............
0
 
KnutsonBMCommented:
try this:

Sub GenSheets()

Dim rng    As Range
    Dim cl     As Range
    Sheets("Blank").Visible = True
    With Sheets("Quotation")
        Set rng = .Range(.Cells(6, 1), .Cells(6, 1).End(xlDown))
        For Each cl In rng
            strname = "OL " & cl.Value
            'Blank is the name of my template worksheet

            Sheets("Blank").Copy After:=Sheets(Sheets.Count)
            Set sh = Sheets(Sheets.Count)
            sh.Name = strname
            sh.Visible = True 'xlHidden

            sh.Cells(3, 2) = "=Quotation!$F$" & cl.Value + 5
            sh.Cells(3, 3) = "=Quotation!$B$" & cl.Value + 5
            sh.Cells(3, 4) = "=Quotation!$C$" & cl.Value + 5
            sh.Cells(3, 5) = "=Quotation!$D$" & cl.Value + 5
            sh.Cells(3, 7) = "=Quotation!$E$" & cl.Value + 5
            Set q = Sheets("Quotation")
            q.Cells(cl.Value + 5, 35) = "='" & strname & "'!$J$31"
        Next cl
    End With
   
       Sheets("Blank").Visible = False
   
    End Sub
0
 
wellsoAuthor Commented:
It still seems to be copying a different sheet sheet as OL1
0
 
KnutsonBMCommented:
aaaaaaah, i see what you are saying now, sheets OL 2 through 15 seem to be correct though right?  just OL 1 that is messed up
0
 
wellsoAuthor Commented:
yes thats right, sorry for the mix up
0
 
KnutsonBMCommented:
This seems to work now, it was a problem with it renaming the last sheet (one that was hidden) and making it visible

Sub GenSheets()

Application.ScreenUpdating = False
Dim rng    As Range
    Dim cl     As Range
        For Each WS In Worksheets
        WS.Visible = True
        Next
    With Sheets("Quotation")
        Set rng = .Range(.Cells(6, 1), .Cells(6, 1).End(xlDown))
        For Each cl In rng
            strname = "OL " & cl.Value
            'Blank is the name of my template worksheet

            Sheets("Blank").Copy After:=Sheets(Sheets.Count)
            Set sh = Sheets(Sheets.Count)
            sh.Name = strname
            sh.Visible = True 'xlHidden

            sh.Cells(3, 2) = "=Quotation!$F$" & cl.Value + 5
            sh.Cells(3, 3) = "=Quotation!$B$" & cl.Value + 5
            sh.Cells(3, 4) = "=Quotation!$C$" & cl.Value + 5
            sh.Cells(3, 5) = "=Quotation!$D$" & cl.Value + 5
            sh.Cells(3, 7) = "=Quotation!$E$" & cl.Value + 5
            Set q = Sheets("Quotation")
            q.Cells(cl.Value + 5, 35) = "='" & strname & "'!$J$31"
        Next cl
    End With
   
       Sheets("Blank").Visible = False
       Sheets("DBase").Visible = False
       Sheets("Forgings").Visible = False
       Sheets("GSC1").Visible = False
       Sheets("NOV 2007").Visible = False
       Sheets("Wghts").Visible = False
       Sheets("Drill").Visible = False
       Sheets("Pies").Visible = False
       Sheets("Additional Mtrls").Visible = False
       Sheets("CF8M LP").Visible = False
       Sheets("CF8M HP").Visible = False
       Sheets("CA15 LP").Visible = False
       Sheets("CA15 HP").Visible = False
       Sheets("DBase2").Visible = False
       Sheets("Incolloy 825").Visible = False
       Sheets("Inconel 625").Visible = False
       Sheets("SMO").Visible = False
       Sheets("UNS 31803").Visible = False
       Sheets("UNS 32760").Visible = False
'       Sheets("CF3M").Visible = False

Application.ScreenUpdating = True

   
    End Sub
0
 
wellsoAuthor Commented:
Thanks for all your help everyone
0
 
KnutsonBMCommented:
???????  ha guess mine wasn't good enough, cheers chris
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
KnutsonBM

I was working on the same issue in paralell for a while before I decided to take the copy to index 1 then move route.

As such it may have the same result as your post but it does use the original structure, (i.e. no sheets to unprotect so no overhead for that activity at run time or when adding / removing / renaming sheets) so as I see it it would be easier for the author to work with ... so given it was not a clone of your effort I see no issue with the authors preference.

I am not gainsaying your solution, merely explaining why I felt my alternative was a reasonable post in the first place given your last post.

Chris
0
 
KnutsonBMCommented:
I was in no means saying my answer was better than yours so please don't take it that way, i am merely a novice with it comes to excel and I have a lot to learn, i find the best way for me to learn is by answering other peoples questions, i feel that gives me the broadest range of knowledge rather than just working on my own projects, i am more challenged this way.  Your answer is much cleaner and more efficient that mine and I have absolutely no problem with the points going to you!  Was just trying to make a small joke is all.

-Brandon
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Hi Brandon

Apologies if I was OTT, I was concerned you felt your post was the better, ... different yes but that is all.

As for learning by answering questions ... absolutely!  I still learn a ton from my betters here and I have been 'bettered' so often I am all but immune to embarassment ;o)

Chris
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.