Report

Excel Formula/VBA Assistance

Ask a question SFRIAR616 6Posts Friday September 15, 2017Registration date November 14, 2017 Last seen - Last answered on Nov 14, 2017 at 05:17 AM by vcoolio
Good Morning,
I would appreciate some advice/assistance with formulas and VBA code.

http://ge.tt/3wxXEJn2

I am trying to create an equipment tracking workbook (a copy of which I have attached for your assistance/advice).
What I am struggling with is:
creating a formula which will automatically copy the locations from the Equipment Mobilisation Details/Equipment Return Details Sheets across to the Available Equipment/Mobilised Equipment Sheets (I am not sure if this would be possible as the same serial number will appear several times in the mobilisation data sheet as it moves to different locations).

I would then like to automatically update the Master Equipment List with the changes and have the equipment removed from the Available Equipment/Mobilised Equipment Sheets

Any advice/assistance would be greatly appreciated.

Thank you.
Helpful
+0
plus moins
Thanks for posting.

Understand, Excel cannot manipulate data (make changes to other cells on the sheet/alter values), unless you press a button to trigger it.

I have wire framed this type of manipulation here:
http://ccm.net/faq/53497-how-to-manipulate-data-in-excel-using-vba


Cut and paste of a row. Stuff like that!
SFRIAR616 6Posts Friday September 15, 2017Registration date November 14, 2017 Last seen - Nov 14, 2017 at 04:10 AM
Thanks for the quick response ac3mark,
I am a bit of a layman when it comes to VBA, I can do the basic stuff like linking the spreadsheets, but when it comes to the more complex stuff its way above my understanding.
I have had a look at the link you sent me and it has totally confused me.
Could you explain to me in layman terms and suggest any online courses which I could undertake which would give me a better understanding of vba.

Thanks,
Reply
Leave a comment
Helpful
+0
plus moins
Hello SFriar616,

Further to what Mark said, you could probably use a Worksheet_Change event in the two source sheets to send the data to the relevant destination sheets. You could use a separate criteria (such as, to be original, OK) so that you only send the individual rows of data that you want to the destination sheets.

From here (the destination sheets), using the same criteria brought over from the source sheets, you could send the relevant data to the Master sheet.

As for clearing the data from the Available Equipment/Mobilised Equipment Sheets, the code that you have in Module 1 (MoveStuff) will do that each time that you run that particular code. This line in that code:-

Sheets(ar(1, i)).UsedRange.Offset(1).ClearContents


clears these sheets prior to any new data being transferred across.

Just an idea for you to ponder.

Cheerio,
vcoolio.
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!