Copy grouped cells based on typing a word in another cell

**2Js** · 06-02-2015, 04:53 PM

I am trying to copy a group of cells that is another tab by simple typing a word "New". For example, my grouped cells is called 'Project' that is located on the tab called 'Data'. On my tab that is called 'Project List' I would like to type the word "New" (for example in cell 'A1' and have it copy the group of cells exactly how it is on the tab 'Data'...

Once I get that part done I have another part of this that I will need help on...one thing at a time or my head will implode.

Engineering Project List.xlsx

**xladept** · 06-02-2015, 06:59 PM

Hi JJ,

Welcome to the Forum!

Here's an event procedure for your Project List sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target = "New" And Target.Address = "$A$1" Then
Sheets("Data").UsedRange.Offset(1).Copy Range("A2")
Else: UsedRange.Clear
End If: Application.EnableEvents = True: End Sub

Directions for running the routine(s) just supplied

If you haven't used macros before you'll need to go to:
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)

Then - Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Click on the sheet

Type "Option Explicit" then paste the code under it

Save the workbook as Macro-Enabled and you're ready to go

**2Js** · 06-03-2015, 07:31 AM

First off thank you for your help and I am one of those Excel users that does not get too far into the VBA stuff. I did some things in the past, but my memory always gets it wrong when I try it some years down the road. I guess I just need to do it more?

Okay so that sort of worked, couple of things on this. I don't won't to copy everything on the "Data" sheet. I only want it to copy cells A55 through J56, which I have grouped as "Project" in the name window. Also, if it is possible I would like for when it is copy to overwrite the word "new". In other words wherever I type the word "New" is where it will start the paste.

**2Js** · 06-03-2015, 09:27 AM

Okay I am going at this all wrong and need to simplify this for the end user.

So with that, here is the scenario, User types in a “named” value in any of the cells on the tab labeled “Project List”. These “named” values are listed as “Oven”, “Oven_Comp”, “Comp”, “Heater”, “GPS”, and “Controls” and are in the tab listed as “Data”. When user types any one of these names in a cell located on the “Project List” tab it will copy that named group cells exactly as it formatted in the tab “Data”

For example, we are working on a some new projects in engineering let’s two. An Oven project, and a Heater project. Starting with the first one, the user types in “A1” on the “Project List” tab “Oven”. Excel will copy all of the named group cells called “Oven” that is located in the “Data” tab and paste it, starting with “A1” in the “Project List” tab (overwriting the word that was just typed and resetting the macro). After that is done he may skip down A16 on the “Project List” and type “Heater”. Again, Excel will copy all of the grouped cells called “Heater”, once again overwriting the word he just typed in that cell. This can keep going, on and on.

If for some reason the user did not mean for that typed word after he hit enter he should be able to undo it by clicking undo or ctrl-Z or at least selecting it and deleting it.

Does any of this make since? I have attached an updated spread sheet so you can see my layout.

**xladept** · 06-03-2015, 07:50 PM

Hi JJ,

The Forum was impacted by the flawed security app that they've (recently?) installed - I'll see what I can do tomorrow.

**xladept** · 06-04-2015, 06:44 PM

Hi JJ,

Try this and let me know:Engineering Project ListJJ.xlsm

**2Js** · 06-05-2015, 08:15 AM

Hey this is it! Thanks a lot!

One issue though, it only allows it to do once for each type. It could be that I am working on more than one of same type. Is there a way to add line of code so it can just repeat this routine over and over again?

**xladept** · 06-05-2015, 01:21 PM

Hi JJ,

Thanks for the rep!

Sub JJAdams(T As Range): Dim wd As Worksheet, Names, N As Long, F As Range
Names = Array("Oven", "Oven_Comp", "Comp", "Heater", "GPS", "Controls")
For N = LBound(Names) To UBound(Names)
If T.Value = Names(N) Then GoTo Proceed
Next N: Exit Sub
Proceed:
Application.EnableEvents = False: T = ""
Set wd = Sheets("Data"): wd.Range(Names(N)).Copy T
Application.EnableEvents = True
End Sub

Copy grouped cells based on typing a word in another cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copy grouped cells based on typing a word in another cell

Re: Copy grouped cells based on typing a word in another cell

Re: Copy grouped cells based on typing a word in another cell

Re: Copy grouped cells based on typing a word in another cell

Re: Copy grouped cells based on typing a word in another cell

Re: Copy grouped cells based on typing a word in another cell

Re: Copy grouped cells based on typing a word in another cell

Re: Copy grouped cells based on typing a word in another cell

Thread Information

Users Browsing this Thread

Similar Threads

Copy certain cell data based on word

[SOLVED] Copy specific cells based on one word in the cell, to a different worksheet

copy rows including the grouped cells from another worksheet

Excel 2007 : change cell colour based on grouped values

Copy and paste in other cells while typing in different cells

Bookmarks

Bookmarks

Posting Permissions