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

[Fr0mi]

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

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

[westconn1]

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?

[Fr0mi]

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

[Fr0mi]

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:

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

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

[westconn1]

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?

[Fr0mi]

Thanks for the reply!

If I take this away:

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:

' 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:

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

[westconn1]

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

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)
        previdx = ActiveSheet.Index  ' or you can just increase the counter by 1
        Set NewSheet = ActiveSheet ' New Sheet Created and Set
End If

[Fr0mi]

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

previdx = ActiveSheet.Index

Method.
The only piece of code I added was:

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

[westconn1]

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

for ni = 0 to combobox1.listcount - 1
      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
              activesheet.name = combobox1.list(ni)
              Set NewSheet = ActiveSheet
      Else
              ' Copy Model Sheet into the already created New WB
              traffic.Copy After:=NewWB.Sheets(combobox1(ni - 1)
              activesheet.name = combobox1.lis(ni)
              Set NewSheet = ActiveSheet ' New Sheet Created and Set
      End If 
next

[Fr0mi]

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:

NewSheet.Cells(3, 8) = ColabsArray(NI)

Regarding the Loop:

This is checking the form if only one Salesman Sheet should be created or all.

    ' 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:

        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.

[westconn1]

i can not see why the sheets are not created in the correct order, turns out you missed line 13 in post #7

[Fr0mi]

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:

application.update = false

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

[westconn1]

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

[Fr0mi]

Perfect!
Must have over read your post somehow..

Well it works fine now, great help!