Results 1 to 14 of 14

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

Threaded View

  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 10:43 AM. Reason: resolved

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
  •  



Click Here to Expand Forum to Full Width