Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel VB - Copy worksheet

Posted on 2010-11-30
19
289 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

861 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