New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Need Help W/ Code for Work Project

Macros, VBA, Excel Automation, etc.

Need Help W/ Code for Work Project

Postby laurensims41 » Wed Feb 03, 2016 5:37 pm

I am trying to create a macro that when a specific type of unit is choose from a drop down box in a cell it takes the name of that cell and looks up the sheet it correlates with and copy's and pastes the contents in a final spreadsheet.
There are four types of variables and each has at least three choices. So there are many combinations.

Example.
HVAC: Bard or Marvair
Generator: MTU, Katolight, Cummins
Environmentals: Yes,NO
Fuel: Diesel,Propane

Each of these chosen will go to a specific sheet and copy the data in that sheet and paste it to another sheet.
My command button will save the file sheet to the desktop of the computer using the sheet.

Any help on where to start would be so helpful. I have been trying different things but nothing is truly working in the way I want it.
  • 0

laurensims41
Rookie
 
Posts: 15
Joined: Feb 3, 2016
Reputation: 0
Excel Version: Office Professional Plust 2010

Re: Need Help W/ Code for Work Project

Postby pecoflyer » Thu Feb 04, 2016 2:42 am

Hi and welcome to the board

Please post a sample sheet showing your data and any progress you already made.
Thank you
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Re: Need Help W/ Code for Work Project

Postby laurensims41 » Thu Feb 04, 2016 3:55 pm

I am sure there aer tons of different ways to go about this and get the same result. The way I have the spreadsheet set up I think will make it the easiest for the users that have to use it. If you think there is an easier way to code it other then using the drop down cells I am up for anything. I have been starring at this trying to think of the best way to make this work with the least amount of code.

I attached the spreadsheet.
  • 0

You do not have the required permissions to view the files attached to this post.
laurensims41
Rookie
 
Posts: 15
Joined: Feb 3, 2016
Reputation: 0
Excel Version: Office Professional Plust 2010

Re: Need Help W/ Code for Work Project

Postby NoSparks » Fri Feb 05, 2016 12:02 pm

Mind elaborating as to what sheets should be copied based on each drop down ?

HVAC
Bard = sheet "Bard HVAC"
Marvair = sheet "Marvair HVAC"

Generator
MTU = ?
Katolite = ?
Cummins = ?

Fuel
Diesel = ?
Propane = ?

Environmentals
Yes = ?
No = ?
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: Need Help W/ Code for Work Project

Postby laurensims41 » Fri Feb 05, 2016 12:49 pm

I have been playing around with using option button's grouped into the categories to make it simpler. the only problem with that is I if they click on a differn't option button I want it to delete what was just pasted to the finished sheet. I attached a clearner version of the spreadsheet with the correct sheets on it. Some of the sheets don't have values in them yet I haven't written that yet.
Do you think the option buttons would be easier? Rather then the drop down. Even if the user chooses the wrong drop down i would need that deleted from the finished sheet.

HVAC
Bard = sheet "Bard HVAC"
Marvair = sheet "Marvair HVAC"

Generator
MTU = MTU Onsite
Katolite = Katolite
Cummins = Cummins

Fuel
Diesel = Diesel
Propane = Propane

Environmentals
Yes = Environmentals
No = Not linked to any sheet
  • 0

You do not have the required permissions to view the files attached to this post.
laurensims41
Rookie
 
Posts: 15
Joined: Feb 3, 2016
Reputation: 0
Excel Version: Office Professional Plust 2010

Re: Need Help W/ Code for Work Project

Postby NoSparks » Fri Feb 05, 2016 1:22 pm

Do you think the option buttons would be easier? Rather then the drop down.

No. Everything can all be dealt with ahead of writing to the Finished Sheet.

It's just necessary to know what goes with what and what doesn't.
ie: if I select Cummins as the Generator is Propane an acceptable fuel ?
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: Need Help W/ Code for Work Project

Postby laurensims41 » Fri Feb 05, 2016 1:30 pm

Yes could be propane or diesel.
  • 0

laurensims41
Rookie
 
Posts: 15
Joined: Feb 3, 2016
Reputation: 0
Excel Version: Office Professional Plust 2010

Re: Need Help W/ Code for Work Project

Postby NoSparks » Fri Feb 05, 2016 1:41 pm

Okay, I've just seen your user form (I'm very partial to user forms), I'll come up with a user form solution.
Will take a little while of playing around.
Hang tight.
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: Need Help W/ Code for Work Project

Postby laurensims41 » Fri Feb 05, 2016 1:46 pm

Thanks for taking a look into this for me. I was messing with the user form in hopes it would make coding a bit easier for myself.
  • 0

laurensims41
Rookie
 
Posts: 15
Joined: Feb 3, 2016
Reputation: 0
Excel Version: Office Professional Plust 2010

Re: Need Help W/ Code for Work Project

Postby cchapman » Fri Feb 05, 2016 4:32 pm

Are you open to using your spreadsheet as it is with the button and in-cell dropdowns on the Cover sheet rather than the userform route?

If so, I have some code for the button that might do what you want - this does rely on helper cells listing each source sheet name (ie the sheet where the data is to be copied from), but these helper cells can be easily hidden.

Let me know if you want to go down this route, and I'll give you the code for the button.

Chris
  • 0

cchapman
Rookie
 
Posts: 1
Joined: Feb 4, 2016
Reputation: 0
Excel Version: 2010/2013

Next

Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 35 guests