[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel VB - Copy worksheet

Posted on 2010-11-30
19
Medium Priority
?
298 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
17 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 35

Accepted Solution

by:
Norie earned 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

834 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