Automatically copy an entire sheet from one .xls to another

[damian_r_Home]

Morning all.

I ideally need to take a specific sheet from a specific document and copy it's entire contents, formatting, filters etc into a new xls such that when i open the new xls it will get all the data once more from the original sheet.

I know that you could simply set the new xls to copy it over on a cell by cell basis (such as "='[original.xlsm]Sheet1'!G29" in cell G29 of the new sheet etc) but this doesn't copy over all the filters and formatting.

Is it even possible to do this?

Thanks in advance

D

 

[JazzSP8]

You can right click on the Sheet name in the Original Document and then select "Move or Copy".

This will give you a Dialog Box with several options for selecting sheets and a Workbooks that are open.

At the bottom left there is a check box for "Create a copy" - Using this will let you take a full copy of the Sheet to either an existing Workbook, or to an entirely new one.

Does that help?

 

[damian_r_Home]

Hi Jazz,

I know you can copy a sheet that way but the duplicate sheet won't keep up-to-date with the master sheet.

The thing is that I need to access a particular sheet on a document that is used by our document controller and then use it to filter out rows that are applicable to the area of the project that i'm working on. I don't need to change any data in this, just use it to track what applies to me more easily than trying to get into a document that others are in and out of all day long (they have to add data to various other sheets in the master document) and then filter the sheet i need to show what is relevant to me.

D

 

[JazzSP8]

Ah - Got you

Yeah you can set something like that up using VBA, if it's literally the same thing every day then you'd probably get a fair amount of the code from just 'Recording' yourself doing it.

Are you familiar with doing things like that?

 

[footoo]

Put this in the Sheet("Copy") module :

Private Sub Worksheet_Activate()
Sheets("Master").Cells.Copy Sheets("Copy").Cells
End Sub

 

[damian_r_Home]

Jazz,
Its been yonks since I've done even the most basic VBA

Footoo,
Thanks I'll give that a go in the next hour (is there a way to add a path so it can find the master file?)

 

[JazzSP8]

Jazz,
Its been yonks since I've done even the most basic VBA

It's like riding a bike

When you get the chance, record the actions you'd take when copying the sheet across to your working Workbook and go from there - No doubt it'll need some work for optimisation but once we have the basics it'll be easier

 

[damian_r_Home]

Hi All, me again.

I've been looking into using the built in Data/Connection method of sheet duplication ( https://support.office.com/en-us/ar...8c-0c86ce62b4f5#ID0EAADAAA=Office_2010_–_2013 ) but sometimes when I'm in the workbook that the data is duplicated into, the document the data comes from wont open because it is locked by another user (there aren't any other users as i'm doing test spreadsheets first and therefore I know nobody else is in them)



As mentioned back up the thread I need to get the data from another workbook which other people are in and out of all day long and I can't afford for them to be locked out whilst i'm in mine looking at the data I've copied in.

Is there a way to stop them from being locked out?

 

[damian_r_Home]

Hi all,
Is there anyone that can help with this please?

 

[JazzSP8]

Is there a way to stop them from being locked out?

Open it as Read Only?