Solved

Excel copying wrong sheet in VBA loop

Posted on 2011-03-21
23
363 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:wellso
  • 9
  • 7
  • 4
  • +1
23 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 35180252
OK so which sheet do you want to copy and which sheet is it actually copying?
0
 
LVL 2

Author Comment

by:wellso
ID: 35180326
It keeps copying a dialog sheet for some reason, or the last sheet in the book if I delete that sheet
0
 
LVL 2

Author Comment

by:wellso
ID: 35180585
The sheet I wanted to copy was called 'Blank'
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35180674
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
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35180696
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
 
LVL 24

Expert Comment

by:StephenJR
ID: 35180712
Your code does copy 'Blank'. Can you post a workbook?
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35180717
Sorry chaps, hadn't seen your replies.
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35180928
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
 
LVL 2

Author Comment

by:wellso
ID: 35180938
Heres the file, sorry for the delay
Original-Draft---Copy--3-.xls
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35180977
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
 
LVL 2

Author Comment

by:wellso
ID: 35180999
Its hidden but I want to unhide the copies as they are created please
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35181016
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
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35181044
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
 
LVL 2

Author Comment

by:wellso
ID: 35181147
It still seems to be copying a different sheet sheet as OL1
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35181166
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
 
LVL 2

Author Comment

by:wellso
ID: 35181174
yes thats right, sorry for the mix up
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35181290
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
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 35181356
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
 
LVL 2

Author Closing Comment

by:wellso
ID: 35187176
Thanks for all your help everyone
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35187787
???????  ha guess mine wasn't good enough, cheers chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35187952
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
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35187965
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35188027
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now