• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

Excel VB - Copy worksheet

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
cbsbutler
Asked:
cbsbutler
  • 5
  • 5
  • 5
  • +2
1 Solution
 
SiddharthRoutCommented:
Yes, when you open the destination workbook, open it in invisible mode. What I mean is set it's .Visible property to "False" :)
0
 
Rory ArchibaldCommented:
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
 
ExcelevedCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
cbsbutlerAuthor Commented:
Hi Exceleved,

I'd like to copy the worksheet "Template" to the same workbook. I don't mind where it's placed.
0
 
SiddharthRoutCommented:
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
 
cbsbutlerAuthor Commented:
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
 
Rory ArchibaldCommented:
Works fine for me, though line 3 should actually be:

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

Open in new window

0
 
cbsbutlerAuthor Commented:
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
 
Rory ArchibaldCommented:
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
 
SiddharthRoutCommented:
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
 
Rory ArchibaldCommented:
I was being lazy and forgot to alter line 5:
Sheets("Template").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
0
 
SiddharthRoutCommented:
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
 
SiddharthRoutCommented:
Yeah I just tested it and it works :)  Good solution!!!
0
 
cbsbutlerAuthor Commented:
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
 
Rory ArchibaldCommented:
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
 
NorieVBA ExpertCommented:
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
 
cbsbutlerAuthor Commented:
I have solution
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 5
  • 5
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now