Getting Data From Last Years Accounts Workbook

[Matt53]

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

Martyn

 

[Matt53]

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.