Macro for exporting data out of different sheet

[gingelp]

Hello,

I need help! I need a macro which uses 2 sheets. I have a value in Sheet1("F30") which then needs to be found in Sheet2("A:A") i then need it to copy the selection along that row which would be ("B/row number , I/same row number")
I cant use copy entire row. I then need the selected copy to paste back into Sheet1 but the location would be ("G30 , N30")

Forgot to mention, This is done using a button and the value is written in.

If anyone can help that would be great if I am missing any information that you might need I am happy to try supply it

Many thanks
Liam

[pipoliveira]

Hi Liam,
Trust you're doing well.
Would it be possible to attach a sample file so we can check it out and provide some help?
Cheers,
Filipe

[gingelp]

Here is a sample, i have had to rip a few things from it though im afraid

[pipoliveira]

Hi Liam,
Check the attached file.
I have created a named range in equipment monitor sheet and added a code in the Export button to perform a lookup and add it in the required cells.
Please let me know if that's what you're looking for.
Best regards,
Filipe

[pipoliveira]

Sorry Liam,
Check the one attached here.
I added a bit more code that displays a message box in case the EQ Number does not exist in the Equipment Monitor Table.
Cheers,
Filipe

[gingelp]

Hi Filipe,

This is amazing thank you so much! I like the touch for a warning for if the EQ part doesnt exist

Many thanks
Liam

[pipoliveira]

You're welcome Liam.
Let me know if you need explanation on the written code.
If you don't need more assistance, please set this thread to Solved under Thread Tools.
Best regards,
Filipe

[gingelp]

If you can give an explanation i would appreciate it.

From what I can understand it is using the range EQ Number for titles and then locating the required EQ value after that its just importing the rest of the row into the required area

Is that close?

Regards
Liam

[pipoliveira]

Yes Liam,
First thing I did was setting a Named Range in Equipment Monitor sheet under the name "EQ_Number". I set the range as "A2:I1000" (You can change this if you're going to have more records in the future).
Then, in Module4 I have added the code (Under name Export_EQ_Value) as explained here:

Please Login or Register  to view this content.

Then just assigned this code to the Export button in Main Menu sheet.
Let me know if you need further assistance.
Best regards,
Filipe

[gingelp]

Okay so I am slowly learning which is great to know!

So how would I go about re importing the line back in to update the information? Also is there a way to recognize the department in H30 so it can also find the same EQ value and update the information there as well?

Regards
Liam

[gingelp]

I was thinking if i get it correctly with a new button I could essentially reverse what you have written so that it will then find it and then copy the main menu to the equipment list. after it reads H30 and follows the same procedure as it did for copying it into the equipment list. Would this require more ranges? Or can i successfully say I am going along the right tracks?

Regards
Liam

[pipoliveira]

If you want to be able to update information within a EQ Number, I would suggest using a UserForm for it so it can copy the info to the Main Menu and/or update new info of that EQ to Equipment Number.
This can also be done for H30.

[pipoliveira]

You can also,
On the code, just swap from this

Please Login or Register  to view this content.

To this:

Please Login or Register  to view this content.

Need to try out to see if that really works but I think so.
Cheers,
Filipe

[gingelp]

Hi Filipe,

I tried it out but comes up with wanting an expression at the end of the code line where it says .Value = 'Equipment Name

Does the code essentially reverse the export by importing it back into the same area as where it was taken from?

Regards
Liam

[pipoliveira]

Hey Liam,
Let me have a look and I'll get back to you.
Cheers,
Filipe

[pipoliveira]

Hi Liam,
Copy-Paste this code on a Module or even below the code I've made previously, then assign the macro to a button (Maybe name it as "Update Info" or something).

Please Login or Register  to view this content.

If you change data in Main Menu, running the Macro it will update the info in the "Equipment Monitor Sheet".
The code is a little more complex than previous one.
Let me know if that works.
Cheers,
Filipe

[gingelp]

Hi Filipe,

Thank you so so much for your help! This is the last little push I needed

Many thanks
Liam