# Copying worksheet (containing formulae & data) to another workbook, to use for the next yr

#### PurrSent

##### Board Regular
Hi

Using Excel 2010 on Windows 10

I have a Workbook for 2018, Sheets are Jan - Dec, ie 12 sheets, plus a sheet containing data, ie named payees (income) and recipients (outgoings), that has been 'entered' from the previous month, plus manually entered too.

There is a 'Master' sheet which takes this above data from Dec 2018 sheet, ie sources re Income and destinations re Outgoings. The formula in 'Master' sheet is, for example "{=Dec!C16}".

I thought I'd copy this 'Master' sheet, which contains data that needs to be continued into Jan sheet of the next year, ie 2019, and paste into a new sheet, named 'Master2018' . Then, in Jan (2019 Workbook) I thought I could use a similiar formula to catch that 2018 data, and have it displayed in the Jan 2019 sheet, then be ready to continue with the 2019 accounts.

So -
- I entered '=' into the first required cell in the Jan 2019 sheet,
- clicked on the relevant cell in the new 'Master 2018' sheet,
- then clicked 'Enter' (also tried Shift+Ctrl+Enter)
- the formula showing was "='[P****Accounts 2018 Rev 8-12-18.xlsm]Dec'!C16"
- but, the data in the cell in Jan (2019) is "='2018'!B6" rather than the data, which should be a name, ie Joe Bloggs.

I've clearly not done something correctly, due to my limited knowledge. I hope you can understand my post / scenario

I appreciate any help. Thanks very much

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### PurrSent

##### Board Regular
Hi
I was really hoping there was a simple solution to this issue and that someone would post a response
I'm still hopeful someone can help me out with this ..... please
Many thanks

#### Logit

##### Well-known Member
.
Does the MASTER sheet differ from the JAN sheet ?

#### PurrSent

##### Board Regular
.
Does the MASTER sheet differ from the JAN sheet ?

The Master sheet is designed to 'pick up' data from 'December' (2018) sheet, by formula in each cell such as "{=Dec!C22}" to 'pick up' the Payee's Name. By the same method, other data is 'picked up' such as Gift Aid status.

On this Master sheet, I have an 'Income' section, being 3 columns - for Name, ID and Gift Aid Y/N. There are 75 entries (Names/Rows etc) in Income, of which the majority are regular names, hence wanting to minimise manual entry into Jan 2019. Outgoings has a lesser number of names entered but, again, many will be repeated each month.

Jan (2018) sheet doesn't have the above forumula in, as 2018 was the first year I have created the spreadsheet. Feb (2018) to Dec (2018) have the equivalent formulae, ie "{=Dec!C22}", to bring forward the entry from the previous month, ending up in Dec (2018) unless deleted on a month sheet.

I had hoped to be able to select the data from Master (2018) sheet and paste into a new sheet in the 2019 Workbook, with Jan (2019) picking up the data, so as to continue to carry it forward month by month

So, yes, I feel the Master sheet is 'differen't in that the data is 'picked up' using formulae so, when pasted into new sheet in 2019 workbook, all I get is a formula pointing to the 2018 workbook, rather than just the 2019 Mastersheet.

I do hope this makes sense. I'm finding it very difficult to explain.

Many thanks

#### pedro-egoli

##### Well-known Member

I have copied a sheet from previous year for solar statistics
Have tabs with the name year (eg Jan 2019) and a master sheet which collects MTD data from these tabs.
The format for Month name on mastersheet is custom "mmm" and is entered as 1/01/2018
This is what it looks like
Excel Workbook
ACDMNO
3days303131
4SUMMARY OF SOLAR PRODUCTION / USAGE ETCJANFEBNovDecJan
5TARGET PRODUCTION MTD  98210341016
6ACTUAL PRODUCTION MTD  106410601134
SUMMARY PV PRODUCTION USAGE (2)

Just look at formulae in M3:O3 and M6:O6 This is how I get the figures from the tabs for each month.
The month shown in row 4 (M4:O4) are entered as "1/11/18" and formatted as custom"mmm"
You will note the change in year in N6 and O6.
That might give you a bit of a guide.

Pedro

#### Logit

##### Well-known Member
.
If pedro-egoli example does not provide the information you require for your workbook.

That way we won't need to guess how to proceed and you will obtain the best answer possible.

For posting an example, you can use DropBox, Google Cloud or something similar, then provide the downlink here.

Thanks.

#### PurrSent

##### Board Regular

Hi guys

Many thanks for trying to help.

pedro-egoli
I really appreciate you taking the trouble to post images of a spreadsheet. It was good of you to spend the time. Unfortunately, your spreadsheet forumlae is much to complicated for me to understand and follow.

Logit
Again, many thanks for your suggestion to upload (to Dropbox) and post the link. I'll bear this in mind in future.

However, I've just had a 'eureka' moment ... So, I've now solved my issue!!!

Something prompted me that there were various options in 'Paste' after Right Click. So, with some experimenting, I've discovered I can do exactly what I want to do by-

Right click on cell where data to be pasted
Hover cursor over 'Paste Special'
Press 'E' on keyboard (Shortcut for 'Paste Values & Source Formatting')
Hey Presto!!!

As I've already mentioned, I am very grateful for all your help. I value all the help available on this forum and is my 'go to' when I have issues using Excel that I can't solve, even after searching the internet.

Thanks guys

Last edited:

#### Logit

##### Well-known Member
Ditto ... glad you solved it.

Cheers

#### PurrSent

##### Board Regular
Thanks

I just felt there should be a way to copy the data captured so as to paste into a new workbook. It hadn't occured to me there may be the option of doing just that in 'Paste Special' as I rarely do other than Ctrl+C / Ctrl+V

So, something else I've learnt ...

Until next problem, eh ..... ???

Thanks again

Last edited:

Replies
4
Views
59
Replies
5
Views
82
Replies
1
Views
77
Replies
2
Views
26
Replies
7
Views
86

1,109,348
Messages
5,528,182
Members
409,807
Latest member
nicky736

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...