Solved

Excel VB - Copy worksheet

Posted on 2010-11-30
19
286 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

914 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

14 Experts available now in Live!

Get 1:1 Help Now