Solved

Excel VB - Copy worksheet

Posted on 2010-11-30
19
285 Views
Last Modified: 2012-05-10
I've noticed when copying worksheets in excel the workbook will automatically "activate" the newly copied sheet.

Is it possible to copy worksheets without "activating" them?
0
Comment
Question by:cbsbutler
  • 5
  • 5
  • 5
  • +2
19 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34238040
Yes, when you open the destination workbook, open it in invisible mode. What I mean is set it's .Visible property to "False" :)
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34238050
No - if you copy, that sheet becomes active (which is necessary in code, or you would have no way of getting a reference to the sheet you copied). You can however simply reactivate whichever sheet you were on.
0
 
LVL 8

Expert Comment

by:Exceleved
ID: 34238116
Try this


Application.ScreenUpdating = False  'to reduce screen flickering
With ActiveSheet
    .Copy           'copy active sheet to new workbook
    .Activate       'return to copied sheet
End With
Application.ScreenUpdating = True

Open in new window

0
 

Author Comment

by:cbsbutler
ID: 34238154
Hi Exceleved,

I'd like to copy the worksheet "Template" to the same workbook. I don't mind where it's placed.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34238175
Sorry misunderstood your question...

Is this what you want?

Sub Sample()
    Application.ScreenUpdating = False
    Sheets("Template").Copy After:=ThisWorkbook.Sheets.Count
    Sheets("Template").Activate
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:cbsbutler
ID: 34238694
The screenupdating command does not stop Excel temporarily displaying the newly copied worksheet before changing back to the "Template" sheet.

I would like Excel to copy the template sheet, without the user seeing this actions.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34238756
Works fine for me, though line 3 should actually be:

Sheets("Template").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

Open in new window

0
 

Author Comment

by:cbsbutler
ID: 34238773
Maybe I'm being too picky, but for a split second the workbook changes the activesheet to the one that's just been copied, then quickly reverts back to the "Template" sheet.

I don't want to see this, I would like it to only display the "Template" sheet.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34238810
I don't see that. If it's really a problem, I suspect you'll need to use the LockWindowUpdate API:


Declare Function LockWindowUpdate Lib "user32" (ByVal hWnd As Long) As Long

Sub Sample()
    LockWindowUpdate Application.hwnd
    Sheets("Template").Copy After:=ThisWorkbook.Sheets.Count
    Sheets("Template").Activate
    LockWindowUpdate 0&
End Sub

Open in new window

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34238813
Maybe I'm being too picky

cbsbutler: No I absolutely understand what you mean. Sometime even I feel why can't 'Something' being done!!!!

Here is a dirty trick. See if this helps...

Sub Sample()
    Dim wBook As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Application.ScreenUpdating = False
    
    Set wBook = ActiveWorkbook
    Set ws1 = wBook.Sheets("Template")
    
    Windows(wBook.Name).Visible = False
    
    Set ws2 = wBook.Sheets.Add
    ws1.Cells.Copy
    ws2.Paste
    
    wBook.Sheets("Template").Activate
    
    Application.CutCopyMode = False
    
    Windows(wBook.Name).Visible = True
    
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34238821
I was being lazy and forgot to alter line 5:
Sheets("Template").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34238829
rorya: I doubt that will work (I have not tested it and I could be wrong... just guessed it as .Copy After:= will always try to activate the new sheet)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34238837
Yeah I just tested it and it works :)  Good solution!!!
0
 

Author Comment

by:cbsbutler
ID: 34238914
Hi rorya,

I'm receiving an error when running the sub... "compile error, Only comments may appear after end sub, end function or end property"
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34238926
What code are you actually using?
The Declare Function... line has to go at the top of your module, before any subs or functions.
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 34252126
This seemed to work for me.

Might have still made the copied worksheet active at some point but not as for the user as far as I can see.
Option Explicit



Sub Button1_Click()

Dim wsNew As Worksheet

Dim rng As Range





    Set rng = ActiveCell

    

    Application.ScreenUpdating = False

    

    Worksheets("Template").Copy After:=Worksheets(1)

    

    Set wsNew = Worksheets(2)

    

    Application.Goto rng

    

    Application.ScreenUpdating = False

    



    

End Sub

Open in new window

0
 

Author Comment

by:cbsbutler
ID: 34489146
I have solution
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

705 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

19 Experts available now in Live!

Get 1:1 Help Now