Getting Data From Last Years Accounts Workbook


New Member
Aug 13, 2017
Hello all,

I have had some great tips etc. from you Excel Experts and am hoping for more. You recently solved my 'INDIRECT' question which works well but now I am looking to copy 'cars in stock' data from a sheet in last years workbook into a stock book sheet in this years workbook. I would like to create a user 'Button' to automate this or just make it simple for the user to manually copy the data across but can't see how. With the INDIRECT function being used, both workbooks will be open at the same time.

Sheet 1 Table - 'Stock Cars' This sheet is totally locked and protected.
(data is mined from the Stock Book which has all cars in stock, cars sold and VAT workings)​

Sheet 2 Table - Stock Book (This years NEW accounts workbook)
This sheet is partially locked and fully protected.​

Sheet 1 has 26 columns of data and a formula to give the number of cars in stock;
SUMPRODUCT(--(STOCKBOOK[Nett Price In Stock]<>0)*(STOCKBOOK[Sold To]="")*(STOCKBOOK[Purchase Price]>0))<strike></strike>​

A formula in each column/row to retrieve the data from the Stock Book;
{IF(ROWS($B$4:$B4)>$B$2,"",INDEX(STOCKBOOK[Stock No.],SMALL(IF(--(STOCKBOOK[Nett Price In Stock]>=0)*(STOCKBOOK[Sold To]="")*(STOCKBOOK[Purchase Price]>0),ROW(STOCKBOOK[Nett Price In Stock])-ROW('STOCK 2016-17'!$A$6)+1),ROWS($B$4:$B4))))}

Sheet 2 has many more columns but the first 26 match the Stock Cars Sheet excepting the formula's.
I need to keep the formulas in this sheet as are, but could paste special the stock car data only?​

I would like to get the user to copy the data in the Stock Cars sheet and paste it into the Stock Book automatically but with protection on it will not paste and I really do not want to unprotect the sheet and give the user access to corrupt the formula's, which could be fatal to the whole workbook.

I cannot maintain the referencing to the old workbook in the new workbook, should I copy the whole sheet across, and do not want the onus on the user to change the source of the data copied across.

I am hoping that you guys can come up with some totally awesome ideas, as usual, or I get the user to manually type each vehicle, that's in stock at the end of the year, into the new years accounts Not a good option for the user but if that's what its got to be!!

I'm sorry for the long post, again, but it's hard to get what I want to explain in just a few words.

Thanks for any help you may be able to offer. Please be gentle with any VBA ideas as I am a total noob to VBA and Macro's



New Member
Aug 13, 2017
Think that I have a formula/macro solution to the above but need help.

I have used sumproduct/Indirect function to get the number of rows from last years stock sheet then copied the headings into this years sheet and used Index/Indirect/Sum/If function for each column header based on the number of rows given in the sumproduct function. This gets the data from the old worksheet and puts it into the new worksheet, which is great, but now I want to create a Macro Button which will copy and paste this variable range of cells from the new worksheet into the Stockbook then Delete the worksheet. I want to delete the worksheet as this will only need to be done once and the Indirect function is very Volatile. The copied data is in a range A3:Z103 named StockData with the sumproduct function in cell F1. The sheet I wish to copy to is called Stock Book and Data rows start at A8. The first 26 columns of the Stock Book have the same headings as the StockData range.

I think this is possible as I have found similar macro's on the forum but I am not good enough at VBA to change them to work with my data. This post looks favourable: Macro to copy and paste a variable range

Any help gratefully received.

Thanks for looking.

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...