Macro - Dynamic Copy Range

**Lacaycer** · 09-14-2012, 08:35 AM

I can't wrap my head around how to write the if statement required to copy a selection of cells if a name exists in the them.

I have a column (T) where the output of a cross reference is either a name or "#NUM!", the names will always be sequential at the top of the column. I need all names to be selected. - This is the part I'm having trouble with.

Once copied it's simply paste values to the first open cell in "F" column of the "EmployeeStatusList" range.

I have other steps required to complete this macro, but as long as I get the code to select the names I can do the rest.

In summary - Select cells from T1 - T50 if contents is other than #NUM! or ""....also in the event that there are no cells the whole process stops,copy nothing.

Thanks

**Jakobshavn** · 09-14-2012, 08:45 AM

How about:

Please Login or Register  to view this content.

**Lacaycer** · 09-17-2012, 02:24 PM

I had to add a column to the copy function

so I modified the macro to:

Please Login or Register  to view this content.

and it worked, the first time. But after saving ans exiting it said Method 'Range of object global fail. Also what needs to be changed to run this macro from another sheet?

**Jakobshavn** · 09-17-2012, 02:39 PM

If the For loop exited immediately, then i would equal 1, and i-1 would equal 0, and:
Range("R2:S" & i - 1).Copy would fail.

Say you want the macro to run on a sheet called "Special" not the sheet you happen to have active:

Please Login or Register  to view this content.

**jaslake** · 09-17-2012, 02:50 PM

Hi Lacaycer

Please wrap your code in Code Tags.
Have you considered using AutoFilter for this exercise?

**Lacaycer** · 09-19-2012, 01:24 PM

Example.xlsx

I tried entering the macro and was unable to make it work more than once. It was either returning the range global error, or pasting the column title. So I figured if you could see what I was trying to do or how it is actually applied, as well as explaining what I'm trying to achieve I might have better luck in solving this problem

**jaslake** · 09-19-2012, 08:04 PM

Hi Lacaycer

Far as I can tell the code in the attached does this:

Copy the names in $Q$2:$Q$49 that are not present in $F$2:$F$49
Past names into the next empty cells in Column "F"
Also transpose names into next empty cells in row of "Master Matrix" sheet
But if there are no names that are different between "Q" and "F"….Do nothing.

It does not do this as yet:

then sort both named ranges "StatusList" & "MasterList"

Are we on track with what we have?

**Lacaycer** · 09-20-2012, 08:36 AM

It works, I'm sure that's no surprise to you. Thanks alot. I probably could have spent another 8 hours just trying to figure this out.

**jaslake** · 09-20-2012, 10:15 AM

Hi Lacaycer
You're welcome.
Will you be doing the Sort Code? If not please explain how you wish each Named Range ("StatusList" & "MasterList") sorted.

**Lacaycer** · 09-20-2012, 11:17 AM

I was going to attempt to record a macro of me sorting the two ranges alphabetically and then trying to figure out how to apply them to the macro you wrote. But if it's not any trouble, your help would be much appreciated.

Also does it matter at all if the button that runs the Macro is located on another worksheet. In the full file it will actually be located in a tab called "DynamicMatrix"

"MasterList" is sorted in row 3 (Left to right)
"StatusList" is sorted in Column "F"

**jaslake** · 09-20-2012, 05:40 PM

Hi Lacaycer

Sort routines have been added...check them out carefully...they seem to be working as desired but you've got a lot going on. Only you know what YOU want to happen.

Please note:

I've added a Header in Cell F1 of ManpowerHelper
I've changed the Named Range StatusList to include this Header
I've moved the Button to Master Matrix Sheet

The code performs as I expect. Let me know of issues.

**Lacaycer** · 09-21-2012, 12:01 PM

AMORPHOUS MATRIX (ALMOST DONE).xlsm

Here's current state....the whole picture, with an explanation of what I've been trying to accomplish.

**jaslake** · 09-27-2012, 11:57 AM

Hi Lacaycer

You have the following listed as NOT COMPLETE:

Need macro that copy's only the cells in "NewTrainingDates" range where dates have been added and finds the person listed in U4 on the master matrix and paste the data into C5 below their name …. Maintaining the spacing is critical

How are dates getting added...I see the Buttons but see no code yet you indicate this item is Complete?
I don't understand this "Maintaining the spacing is critical"...what spacing?...you mean the Row Height?
Should all existing dates for a given employee be deleted and the new dates added...or should new dates merely update existing dates?

also data validation in U2 of dynamic matrix to select non departmental employee

Where does the Data Validation List live?

Need macro that adds name in U2 to manpower helper and mastermatrix and then sorts…in pretty much the exact same fashion as the macro you wrote.

It seems to me if you add DynamicMatrix cell U2 to ManpowerHelper Column Q and run the previously provided code you'd accomplish this task...what am I missing?
The question I have is how does it get added to ManpowerHelper Column Q...you have formulas in Column Q...should the code overwrite the Formula?

**Lacaycer** · 09-27-2012, 01:11 PM

Ignore the maintaining spacing...I just wanted to make sure that the date is applied to the correct cell on the master matrix page. I'm not sure if I have it set up on the file I posted but In the Newtrainingdates range I have a Vlookup set-up to pull the current dates for the person selected in U4. From there the date button will overwrite the formula in the selected cell with the current date. This entire range can then be copied into row 5 below the matching name in the master matrix sheet.

The data validation list in U2 would be 'ManpowerHelper O2:O300...I just hadn't added it yet.

the formula in column q in just the department personnel indexed...The name should not be added here. The person added from U2 would not be an active department member...they would be highlighted yellow and listed as cross trained on the manpower helper status list. The only reason I even need it is because if a team leader gets a new person and they aren't on the list they often do not train them saying "they weren't on my list". So I'd like to eliminate that excuse and give them the ability to add anyone, and keep track of their training.

Hopefully this makes sense. I really need to get some training...I think I make this all more confusing than it needs to be

**jaslake** · 09-27-2012, 04:03 PM

Hi Lacaycer

It's difficult to build on what you have without SEEING what you have. Include what you have in your upload including code for the Date buttons, the Vlookup to pull existing dates for the person selected in U4, the Data Validation for U2 and, VERY IMPORTANT, WHERE does U2 go and HOW does it get there (I'm unable to fathom this from your description).

I'm happy to help as/if I can...at the moment I'm at a loss.

**Lacaycer** · 09-27-2012, 04:27 PM

when I open the file the Enter date Macro is there and the buttons work. But I'll include it at the bottom. The vlookup for existing dates is in, as well as the formula original in column x. So when training dates are saved, Column u can be copied and pasted into corresponding column in master maxtrix. Then the formula original in column x is copied and pasted into column u.

The name is U2 is to add people who are not currently listed as either cross trained or current on the manpowerhelper.... so it's just referencing the o column of manpower helper. When a name is selected here and the "Add Name" button is pressed, I would like the name to be added to the first available cell in column F of manpower helper and the first available cell in row 3 of the master matrix...and then both lists sorted. If the name already exists, or the data validation selection is blank the button should do nothing.

the add names button should work exactly the same as the update training list button... the "run me" macro you wrote to include adding whichever name is in U2 as well as whatever is in column t of manpower helper that would work too. Essentially it's the same thing, the only difference in the names in column t of manpower helper are filtered automatically and the name in U2 is selected by the team leader.

Sub EnterDate()
'
' EnterDate Macro
'

'
Range("C3").Copy
Dim sName As String
Dim btn As Button
Dim Rng As Range
sName = Application.Caller
Set btn = ActiveSheet.Buttons(sName)
Set Rng = btn.TopLeftCell.Offset(0, -1)
Rng.Value = Range("C3")
Application.CutCopyMode = False

End SubAMORPHOUS MATRIX (ALMOST DONE).xlsm

**jaslake** · 09-27-2012, 06:42 PM

Hi Lacaycer

Please use code tags to surround ANY code you post.

The Macro called by the Date Buttons is 'C:\Documents and Settings\Administrator\Desktop\Amorphous Training Matrix (NEWsept21).xlsm'!EnterDate
I don't have Amorphous Training Matrix (NEWsept21).xlsm...I have the file you uploaded AMORPHOUS MATRIX (ALMOST DONE).xlsm.

Don't know what the issue is but the Date Button Code is not in your upload and I don't have access to that code in your upload.

**Lacaycer** · 09-28-2012, 09:10 AM

AMORPHOUS MATRIX (ALMOST DONE).xlsm

The macro should be included in this file. Sorry I didn't know you had to do anything special to post code.

But just in case here is the enter date macro

Please Login or Register  to view this content.

**jaslake** · 09-28-2012, 01:34 PM

Hi Lacaycer

Assign this code to your Save New Training Button

Please Login or Register  to view this content.

**jaslake** · 09-28-2012, 03:23 PM

Hi Lacaycer

Assign this code to your Add Name Button

Please Login or Register  to view this content.

Macro - Dynamic Copy Range

LinkBack

Thread Tools

Rate This Thread

Display

Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Re: Macro - Dynamic Copy Range

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions