Excel Formula/VBA Assistance [Closed]

Posts
6
Registration date
Friday September 15, 2017
Status
Member
Last seen
November 14, 2017
- - Latest reply: vcoolio
Posts
1248
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 17, 2019
- Nov 14, 2017 at 05:17 AM
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.
See more 

2 replies

Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
September 20, 2019
1402
0
Thank you
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
Posts
6
Registration date
Friday September 15, 2017
Status
Member
Last seen
November 14, 2017
-
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,
Posts
1248
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 17, 2019
205
0
Thank you
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.