asked on

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

StephenJR

OK so which sheet do you want to copy and which sheet is it actually copying?

wellso

ASKER

It keeps copying a dialog sheet for some reason, or the last sheet in the book if I delete that sheet

wellso

ASKER

The sheet I wanted to copy was called 'Blank'

Chris Bottomley

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

KnutsonBM

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

StephenJR

Your code does copy 'Blank'. Can you post a workbook?

StephenJR

Sorry chaps, hadn't seen your replies.

KnutsonBM

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

wellso

ASKER

Heres the file, sorry for the delay
Original-Draft---Copy--3-.xls

KnutsonBM

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.......?

wellso

ASKER

Its hidden but I want to unhide the copies as they are created please

KnutsonBM

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.............

KnutsonBM

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

wellso

ASKER

It still seems to be copying a different sheet sheet as OL1

KnutsonBM

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

wellso

ASKER

yes thats right, sorry for the mix up

KnutsonBM

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

ASKER CERTIFIED SOLUTION

Chris Bottomley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wellso

ASKER

Thanks for all your help everyone

KnutsonBM

??????? ha guess mine wasn't good enough, cheers chris

Chris Bottomley

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

KnutsonBM

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

Chris Bottomley

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