[Resolved]Error while copying one Worksheet multiple time to a new Workbook-VBForums
Results 1 to 14 of 14

Thread: [Resolved]Error while copying one Worksheet multiple time to a new Workbook

  1. #1

    Thread Starter
    Member
    Join Date
    May 2010
    Posts
    33

    Question [Resolved]Error while copying one Worksheet multiple time to a new Workbook

    Hello everyone and in advance thanks for your help!

    Well, basically I the aim is to copy a model worksheet from one existing Workbook to a new Workbook, changing its name and populating it with whatever data.
    Next Step copy the model Worksheet from the Old Workbook to the newly created one and simply add it as a new Sheet. (In the end there should be two Workbooks one with a model Worksheet and the other with X Worksheets)

    Now, I have already done this for a previous project and it works perfectly fine. (Using the same Workbook)
    I now have to do something similiar but using different Data & different Sheets.
    However now I adjusted the codes made a new form & function to do this which now gives me a runtime error..
    The code to copy the Sheets however is exactly the same as before the only change happened in the name of variables..

    Code:
    If WBCheck = 1 Then ' Check if this is the first time copying a worksheet
              ' Copy and Create new Workbook
              OldWB.Activate ' Old Workbook
              Traffic.Copy ' Model Sheet
            
              ' Define Variable for newly created Workbook & Sheet
              ' Copy function should set focus to new Workbook & Sheet
              Set NewWB = ActiveWorkbook
              Set NewSheet = NewWB.ActiveSheet
              WBCheck = 2
    Else
              OldWB.Activate ' Focus Old Workbook again
              ' Copy new Sheet into the Workbook that has been created earlier
              ' Copy Model Sheet "Traffic" Again to the New Workbook
              ' & Change its Name to the Next Name in the Array "ColabsArray"
              Traffic.Copy After:=NewWB.Sheets(ShortName(ColabsArray(NI)))
              Set NewSheet = NewWB.ActiveSheet ' Set Variable for newSheet
    End If
    I keep getting the runtime error on this line
    Traffic.Copy After:=NewWB.Sheets(ShortName(ColabsArray(NI)))

    I have checked that "Traffic" & "ColabsArray" & "NI" all have a valid value.
    "NewWB" I don't know how to check..
    I am guessing there is a problem while defining the "NewWB" variable!

    Thanks for everyones help!!!
    Last edited by Fr0mi; Nov 10th, 2010 at 09:43 AM. Reason: resolved

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,891

    Re: Error while copying one Worksheet multiple time to a new Workbook

    depending where newWb is defined it may have no value at this point, there is no need to activate oldworkbook unless you want to work with the active workbook, which is not my recommendation

    set newWb = workbooks("workbookname.xls")

    what is traffic?
    what i the error you are getting?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Member
    Join Date
    May 2010
    Posts
    33

    Re: Error while copying one Worksheet multiple time to a new Workbook

    Thanks for the Reply Westconn1, I havnt got time right now to test it but will tomorrow morning. (My time^^) I am working on a Spanish laptop so I am not sure what the exact translation is, but your guess seems right.

    It states that some "subindex" is undefined. I guess it is referring to newWB.
    I guess some more explaining is necessary:
    This code is the beginning of a function (Using a function made the code easier to read for me)
    So what my code does is, check the Form and its options and sets some variables..
    Next it calls the function in a loop. The function then checks if it is the first time opening it or not. First time running it should define newWB.

    Writing this I realise I could put that part of the code easily outside of the function.. maybe that will help it.

    If you still have a better Idea I am greatful for it
    One thing I still dont get why does it work in my previous code.. its also a function, that on the first run defines newWB on the second run it uses it. However there I use a slightly modified If statement which also gave me an error in this one (that why I changed it).

    The originial one simply asks:
    If newWB is nothing then...

    Traffic is the name of the model sheet defined in previous code. It is a business report of salesman based on a traffic light system ^^ That should sum it up.

    Thanks again!!

  4. #4

    Thread Starter
    Member
    Join Date
    May 2010
    Posts
    33

    Re: Error while copying one Worksheet multiple time to a new Workbook

    Hello again,

    So I made some changes to my code. Yet I still get an error and I was hoping someone could help me.

    Quick summary of the situation:
    I am trying to copy a Template Sheet from a Workbook multiple times into one new WB. The Sheet should be renamed tempending on an Array. (The array is filled with the names of the salesmen.)

    With my code I keep getting Error '9'.
    I get an error on this line:
    Code:
    Traffic.Copy After:=NewWB.Sheets(ShortName(ColabsArray(NI)))
    - Traffic = OldWB.Sheets("Traffic") ' Traffic is the template Sheet
    - SheetCount & ListCount is used for the Loop
    - FirstCall = True if its the first time running the script
    I want only one New Workbook not one for every Salesman

    Code:
    OldWB.Activate
    If FirstCall = True Then
           FirstCall = False ' First Time is not True anymore
                        
           Traffic.Copy ' Copies Model Sheet (Traffic) to a new WB
                        
            ' Defining Variables for the new WB
            Set NewWB = ActiveWorkbook ' Focus should be on New WB
            Set NewSheet = ActiveSheet
    Else
            ' Copy Model Sheet into the already created New WB
            Traffic.Copy After:=NewWB.Sheets(ShortName(ColabsArray(NI)))
            Set NewSheet = ActiveSheet ' New Sheet Created and Set
    End If
            
    Call CreateSheet ' Call CreateSheet Sub
                    
    If Created = True Then ' If everything worked fine
            SheetCount = SheetCount + 1
    Else
    SheetCount = ListCount
    End If
    ShortName is a function that simply shortens the name if it has more then 30 Characters.

    Thanks for your help. I hope it is not necessary to open a new Thread..

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,891

    Re: Error while copying one Worksheet multiple time to a new Workbook

    Set NewSheet = ActiveSheet
    not required, slows procedure, avoid

    you do not show in your code where NI gets a value, what is the value of NI when error occurs?
    what is the value of ColabsArray(NI)?

    what are the names of the copied sheets, guessing looks like they should be traffic(x) where x is next number, how does this relate to the content of ColabsArray(NI)?

    what does createsheet procedure do?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    Member
    Join Date
    May 2010
    Posts
    33

    Re: Error while copying one Worksheet multiple time to a new Workbook

    Thanks for the reply!

    If I take this away:
    Code:
    Set NewSheet = ActiveSheet
    How does VBA then know what the new sheet is?

    About NI, Traffic & ColabsArray:
    ColabsArray stands for Colaboradores(Co-Workers) which beholds all the names of the active Salesmen.
    Names such as: Francisco Javier Soblechero Saco
    The Array is filled during the form initilization.
    The Form allows to choose a report for a specific Salesman or one for all.
    The one for all is where I am getting the error message.
    It should create a new Sheet for every Salesman.

    Traffic is simply the name of the report method. Comes from traffic lights to show if the Salesman is on track or working badly.

    So the Sheets are named after the Salesman.

    Createsheet proceedure simply fills the new sheet with its acording data. Calculates the number of contracts the Salesman has signed over the last 3 months showing the individual weeks. Also showing name, department, date of incoporation etc..

    Code regarding NI:
    Code:
    ' Checking Form
    ' Radio Buttons - List / By Department / By R.D.D. (Salesman)
    If rad_list = True Then
        Cases = 1
    End If
    
    ' Radio Button - Department
    If rad_dptm = True Then
        If Me.combo_dptm.ListIndex = -1 Then
            ErrorNum = 1
            GoTo ErrorEnde
        End If
        
        ' Set correct Select Case
        Cases = 2
        
        NI = Me.combo_dptm.ListIndex + 1
    End If
    
    ' Radio Button - R.D.D.
    If rad_rdd = True Then
        If Not box_all = True Then ' Check if one Salesman or All are wanted
            If Me.combo_rdd.ListIndex = -1 Then
                ErrorNum = 2
                GoTo ErrorEnde
            End If
            
            NI = Me.combo_rdd.ListIndex + 1
            
            ' Only One Entry Wanted
            Outputs = 1
        Else
            ' All RDD
            Outputs = ColabsImax
        End If
    ' Set correct Select Case
    Cases = 3
    End If
    I am getting the error message when i want to output a report for all Salesmen.

    The NI value is set a bit later and looks like this:
    Code:
    If box_all = True Then
        NI = SheetCount ' All R.D.D. want NI to start with the first Arr Entry
    End If
    SheetCount is set to equal 1
    The code posted in the previous post is in a loop that counts the number of sheets created.
    If it helps I can post the whole loop? (R.D.D. = Salesman)

    The NI value on the error is 2 which is correct and what it should be.
    First Sheet / Salesman was created correctly.

    Thanks alot for helping!!!

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,891

    Re: Error while copying one Worksheet multiple time to a new Workbook

    How does VBA then know what the new sheet is?
    why does it need to know, you do nothing with NewSheet in any of the code posted
    this may just be a problem with posting partial code
    it is not passed to the createsheet procedure unless it is a module level variable, but i am only guessing about these things

    is the sheet is always added after, i assume the next sheet is always the last one in the workbook
    so you could try
    Traffic.Copy After:=NewWB.Sheets(NewWb.sheets.count)
    add after last sheet, without using name of sheet
    this may not be suitable, but you could also return the index of the last sheet copied and use that to add sheet after
    like
    vb Code:
    1. If firstcall = True Then
    2.        firstcall = False ' First Time is not True anymore
    3.                    
    4.        traffic.Copy ' Copies Model Sheet (Traffic) to a new WB
    5.                    
    6.         ' Defining Variables for the new WB
    7.         Set NewWB = ActiveWorkbook ' Focus should be on New WB
    8.         previdx = ActiveSheet.Index
    9.         Set NewSheet = ActiveSheet
    10. Else
    11.         ' Copy Model Sheet into the already created New WB
    12.         traffic.Copy After:=NewWB.Sheets(previdx)
    13.         previdx = ActiveSheet.Index  ' or you can just increase the counter by 1
    14.         Set NewSheet = ActiveSheet ' New Sheet Created and Set
    15. End If
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    Member
    Join Date
    May 2010
    Posts
    33

    Re: Error while copying one Worksheet multiple time to a new Workbook

    First of all thanks a lot really appreciate it!

    I do not want to copy all the code, because it is really quite alot (surely it can be improved in many places as well) Also the code would definitley includes information that is not necessary for you & would be wasting your time.
    Other I do not want to upload the file as it include confidential information, thus its not for everyones eyes.

    So we are stuck with partial code.. I hope we can figure this next one out..

    I have done the changes you described in your post using the
    Code:
    previdx = ActiveSheet.Index
    Method.
    The only piece of code I added was:
    Code:
    ActiveSheet.name = Shortname(ColabsArray(NI))
    So first off I am not getting any error messages anymore and the script "works" fine now.

    However, something I found really strange is the order of the sheets. The order of the sheets should be the order of the Salesmen in the Array "ColabsArray".

    When I open my Form the Drop Down List represents the Salesman in the same order as listed in the Excel Sheet. While initializing the form I populated the list and the array simultaneosly.
    However, the sheets are in the order of:
    Sheet 1 = 1 Array Entry
    Sheet 2 = Last Array Entry
    Sheet 3 = Last - 1 Array Entry
    etc..

    I checked the code, but the NI value starts at 1 and is counted up normally by adding a +1 to the variable at the end of the loop.

    About setting the NewSheet Variable I was using it in the CreateSheet Sub to populate the sheet as I have to constantly jump from the Primary Workbook to the NewSheet..

    If it is ok with you I can up the whole code for you to read, this way a solution might be found much faster.. Yet, it is not finished as this is only a part of a bigger project.. (Might have bits of code that dont seem too relavant for this problem now.)

    Thanks again!!!

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,891

    Re: Error while copying one Worksheet multiple time to a new Workbook

    The only piece of code I added was:
    Code:

    ActiveSheet.name = Shortname(ColabsArray(NI))
    i assumed you were setting that in the createsheet procedure, the error you were getting before indicated that a sheet with the name contained in the array was not found, which of course would make sense if you had not set the sheet name

    i have not seen what type of loop you are using, so i can not pick any problem with it
    why do you use an array for the salesmen, when they are already in a dropdown (is this a combobox?)
    something like this should work ok
    vb Code:
    1. for ni = 0 to combobox1.listcount - 1
    2.       If firstcall = True Then
    3.              firstcall = False ' First Time is not True anymore
    4.              traffic.Copy ' Copies Model Sheet (Traffic) to a new WB
    5.  
    6.               ' Defining Variables for the new WB
    7.               Set NewWB = ActiveWorkbook ' Focus should be on New WB
    8.               activesheet.name = combobox1.list(ni)
    9.               Set NewSheet = ActiveSheet
    10.       Else
    11.               ' Copy Model Sheet into the already created New WB
    12.               traffic.Copy After:=NewWB.Sheets(combobox1(ni - 1)
    13.               activesheet.name = combobox1.lis(ni)
    14.               Set NewSheet = ActiveSheet ' New Sheet Created and Set
    15.       End If
    16. next
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    Member
    Join Date
    May 2010
    Posts
    33

    Re: Error while copying one Worksheet multiple time to a new Workbook

    Okay below you find the necessary extracts of the code regarding the loop.
    I name the sheets in the CreateSheet Sub now.

    I was using the array to populate the new sheets, as at the top of every sheet the name of the salesman is also shown. Is it possible to output it directly from the list??
    The code I use currently is:
    Code:
    NewSheet.Cells(3, 8) = ColabsArray(NI)
    Regarding the Loop:

    This is checking the form if only one Salesman Sheet should be created or all.
    Code:
        ' Radio Button - R.D.D.
        If rad_rdd = True Then
            If Not box_all = True Then ' Check if one Salesman or All are wanted
                If Me.combo_rdd.ListIndex = -1 Then
                    ErrorNum = 2
                    GoTo ErrorEnde
                End If
                
                ' Index Number
                NI = Me.combo_rdd.ListIndex + 1
                
                ' Only One Entry Wanted
                Output = NI + 1
            Else
                ' All RDD
                NI = 1
                Output = combo_rdd.ListCount - 1
            End If
    Loop:
    Code:
            P = 0
            Do
                If OldSheet.Range("ColabStart").Offset(P, 1) = 1 Then ' Only show those that are active
                    OldWB.Activate
                    If FirstCall = True Then
                        FirstCall = False ' First Time is not True anymore
                        
                        Traffic.Copy ' Copies Model Sheet (Traffic) to a new WB
                        
                        ' Defining Variables for the new WB
                        Set NewWB = ActiveWorkbook ' Focus should be on New WB
                        previdx = ActiveSheet.Index
                        Set NewSheet = ActiveSheet
                    Else
                        ' Copy Model Sheet into the already created New WB
                        Traffic.Copy After:=NewWB.Sheets(previdx)
                        Set NewSheet = ActiveSheet ' New Sheet Created and Set
                    End If
                    
                    Call CreateSheet ' Call CreateSheet Sub
                    
                    If Created = True Then ' If everything worked fine
                        NI = NI + 1
                        P = P + 1
                    Else
                        NI = Output
                    End If
                End If
            Loop Until (NI = Output)
    The order of the Sheets is still changed to the one posted in the previous post.

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,891

    Re: Error while copying one Worksheet multiple time to a new Workbook

    i can not see why the sheets are not created in the correct order, turns out you missed line 13 in post #7
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    Member
    Join Date
    May 2010
    Posts
    33

    Re: Error while copying one Worksheet multiple time to a new Workbook

    Well thanks anyway, I cannot figure out why the order of the sheets is like this, however it outputs the data right and the order of the sheets in the end doesn't matter.

    Could you tell me how to stop the the screen from flickering on the run of the application?

    I think it is something like:

    Code:
    application.update = false

    Thank you I guess apart from that this thread is resolved.

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,891

    Re: [Resolved]Error while copying one Worksheet multiple time to a new Workbook

    Well thanks anyway, I cannot figure out why the order of the sheets is like this
    see my post above, you missed one line of code

    application.screenupdating = false
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #14

    Thread Starter
    Member
    Join Date
    May 2010
    Posts
    33

    Re: [Resolved]Error while copying one Worksheet multiple time to a new Workbook

    Perfect!
    Must have over read your post somehow..

    Well it works fine now, great help!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.