copy entire column instead of rows

**shido** · 08-27-2015, 07:05 PM

Hi All,

I am wondering if anyone can help me. I have the following code to move row data to another sheet based on drop down "Awaiting", "Remove","Master List" in column A.

However, I want to change the code completely to copy column instead of rows. For example, I have drop down "Awaiting", "Remove","Master List" in column A to Z. when i select Awaiting, the entire column move to Awaiting worksheet. it possible?

Please advise.

Private Sub Worksheet_Change(ByVal Target As Range)
'http://www.ozgrid.com/forum/showthread.php?t=154637
    Dim strStatus As String
    Dim wsAwait As Worksheet
    Dim wsRmove As Worksheet
    Dim wsMList As Worksheet
    Dim lstrw As Long
     
    If Target.Column <> 1 Then Exit Sub
    If Target.Row = 1 Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
     
    Application.ScreenUpdating = False
     
      
    strStatus = Target.Value
    Set wsAwait = ThisWorkbook.Sheets("Awaiting")
    Set wsRmove = ThisWorkbook.Sheets("Remove")
    Set wsMList = ThisWorkbook.Sheets("Master List")
    lstrw = wsAwait.Rows.Count
     
    If strStatus <> "Awaiting" Then
        Range(Target, Target.Offset(0, 2)).Copy
        Application.EnableEvents = False
        Select Case strStatus
             
        Case "Move to Master List"
            wsMList.Range("A" & lstrw).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
             
        Case "Remove"
            wsRmove.Range("A" & lstrw).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
             
        End Select
         
        Target.EntireRow.Delete
        Application.CutCopyMode = False
         
    End If
     
    Application.EnableEvents = True
    Application.ScreenUpdating = True
     
     
End Sub

**shido** · 08-28-2015, 04:09 PM

Any suggestions.

**Doc.AElstein** · 08-28-2015, 07:26 PM

Originally Posted by shido

Any suggestions.

Hi,
. This sounds dead easy so even i could probably do it, and will take a look Tomorrow , if you wish.
. But it would make it a lot easier if you give me a clear “Picture“of what you want....

. it is always preferred if you try to produce Good before and after “ Pictures “ ( Not Images! ) as well as your explanations so I or anyone else can see exactly what you want..and importantly have some copyable test data to work with.

.
The Before or Befores, should look just as it / they does before running of any macro.
.
. The After or Afters, should be hand filled by you so that it they / looks exactly as you want it to After running of any macro, based on the actual sample data in the Before / or Befores

. Make sure there is just enough data to demonstrate your need. Remember to desensitize the data if necessary, but use the typical Format type and choose your data carefully so that it can be used to test all possible scenarios.
...
To give these "Picture" - a few ways...( The second is what most people do )

. 1 ) use the Forum Tools in my signature to produce screen shots we can copy to a spreadsheet ( NO IMAGES ! )
. 2 ) Post Files
. 2 a) To Attach a sample workbook:
View Pic
http://www.excelforum.com/members/da...ch-a-file.html
http://tinyurl.com/oenwprw

. 2 b) Send over a file sharing site, such as this free thing
Box Net,
https://www.box.com/
http://tinyurl.com/7chr7u8
. Remember to select Share and give the link / links they give.

. 3 c) Only as a last resort, P.M **( Private Message ) . me and i will reply with my Email Addressee so you can send me a file
** To PM me, click on my name in the left hand margin when you are logged in, the rest should be obvious.

Alan

**shido** · 08-28-2015, 09:18 PM

Hi Alan.

Thank you for your reply.

Please see attached sample spreadsheet. I have three worksheet “Master List”, “Awaiting” and “Remove”.
1. Row 1:1 will have dropdown “Master”, “awaiting” and “Remove” in all worksheet.

2. All three sheets have “Name” in A1:Ax”, x is a variable number. However, “Name” will either be same or different name in three worksheets.

3. When the user select “Awaiting” in “Master” worksheet

a. I would like to compare Name list in the Awaiting or Remove worksheet

b. If “Name” matches, Copy selected column from “Master” worksheet and move to the “Awaiting” or “remove” worksheet based on the dropdown selection.

c. However, if “Name” does not match in either of the sheet, update the “Name” by adding row and then copy the selected column.

I hope I have explained the basic functionality of how I want the spreadsheet to work.

Thank you for your time and effort.

Regards

sample.xls

**Doc.AElstein** · 08-29-2015, 04:25 AM

Hi,

Originally Posted by shido

.......hope I have explained the basic functionality of how I want the spreadsheet to work......

. I think i can guess close to what you want, but just to be sure I do not waste time if I have guessed wrong..and to help me .........

.1)

Originally Posted by shido

.....
When the user select “Awaiting” in “Master” worksheet
......

I see no “Awaiting” in “Master” worksheet ???

................................
. 2 )

Originally Posted by shido

.....All three sheets have “Name” in A1:Ax”, x is a variable number. However, “Name” will either be same or different name in three worksheets.

You mean “A3:Ax” ?

... OK. Try this:
. I return a File which I will call “Before”. I have changes the data in Master such that it would help me better to check and debug any program. I have deleted all data in the other sheets
. Please modify that file ( Presumably the other sheets ) so that the data looks as it typically might be based on my data in Master. Remember to include the possibility of all scenarios such as missing names etc, etc,..
. Return that file to me as the Before
.
. Now working on a copy of that Before modify it manually to reflect the changes you want to occur after the running of the code I do for you.
. And explain again carefully what you did or would do to initiate that change.
. Return that second file to me as the After
.
. So I await 2 files from you. ( Unless you feel in the mood to give me another After just to make it crystal clear to me what you want )

Alan
. If you can get that info across I will do it for you today or early tomorrow.

**shido** · 08-29-2015, 02:32 PM

Hi Alan,

Thanks for your reply. And Sorry for selecting “Remove” instead of “Awaiting” in the “master list”. As requested, please see “samplebefore” and “sampleafter” worksheets. Clarification: "A3:A(i)", i is the variable number of rows.

1. Master List: I would like to move “Designation 06” from “Master List” to “Awaiting” worksheet via selecting it from dropdown.
a. To move “Designation 06”, Name list shall be compared at first. Comparing “Name list” in both sheet, we found that “Master List” is missing “Name 4”, whereas, “Awaiting” is missing “Name 5” and “Name 9”.
b. At First, add missing name i.e. “Name 4” in the “Master list”.
c. Secondly, add “Name 5” and “Name 9” in the “Awaiting”
d. Once all names matches in both sheets, Move “Designation 06” from “Master List” to the “Awaiting”.

2. Awaiting: Move “Designation 7” to “Master List” from “Awaiting”. First check is if “Name list” matches in both sheets. If it matches then move selected column. If “Name List” does not match, then add missing names.
a. Same principal will apply in moving “Designation 10” to the “Remove” worksheet.

3. Remove: Move “Designation 13” and “Designation 15” to the “Awaiting”. Also, move “Designation 14” to the “Master List”.
a. Check for missing “name List”, add any missing or new names in the source and designation worksheet.

4. General rule is to compare “Name List” first in the source and destination worksheet and add any missing “name List” to both sheet and then move column to the destination sheet.

I hope I have explained detailed enough. However, if you still require any clarification, please do let me know.
Thank you and Kind Regards.

sampleAfter.xlsm sampleBefore.xlsm

**Doc.AElstein** · 08-30-2015, 04:14 AM

Hi,
. OK I think I am alomost with you now..

Originally Posted by shido

...... However, if you still require any clarification, please do let me know......

. a couple of questions..
. 1) You have shown your names in the "After" shown in order

Using Excel 2007

_Row\^Col	A	B	C	D	E	F	G	H
1							Move to Master List	Move to Master List
2	Name List	Designation 1	Designation 2	Designation 3	Designation 4	Designation 5	Designation 7	Designation 14
3	Name 1	1	11	21	31	41	1	11
4	Name 2	2	12	22	32	42	2	0
5	Name 3	3	13	23	33	43	3	13
6	Name 4	0	0	0	0	0	4	14
7	Name 5	5	15	25	35	45	0	15
8	Name 6	6	16	26	36	46	6	16
9	Name 7	7	17	27	37	47	7	0
10	Name 8	8	18	28	38	48	8	18
11	Name 9	9	19	29	39	49	0	19
12	Name 10	10	20	30	40	50	10	20
13	Name 11	0	0	0	0	0	0	21
14
15

Master

.........
. I guess this is because our imaginary names have a number on the end.
. But for real names, Tom ****, Harry etc., then the added order is unimportant?
.
. so this would be just as valid?

Using Excel 2007

_Row\^Col	A	B	C	D	E	F	G	H
1							Move to Master List	Move to Master List
2	Name List	Designation 1	Designation 2	Designation 3	Designation 4	Designation 5	Designation 7	Designation 14
3	Name 1	1	11	21	31	41	1	11
4	Name 2	2	12	22	32	42	2	0
5	Name 3	3	13	23	33	43	3	13
6	Name 5	5	15	25	35	45	0	15
7	Name 6	6	16	26	36	46	6	16
8	Name 7	7	17	27	37	47	7	0
9	Name 8	8	18	28	38	48	8	18
10	Name 9	9	19	29	39	49	0	19
11	Name 10	10	20	30	40	50	10	20
12	Name 4	0	0	0	0	0	4	14
13	Name 11	0	0	0	0	0	0	21

Master List

Correct?
.....................................................

. 2) Are the destination headings always written such:
Designation 4, Designation 6
. ..etc with a number on the end which is used to determine the order, or is the order of the columns also not important?

Alan

**shido** · 08-30-2015, 05:46 AM

Hi Alan,

I would like to keep the Naming Order, whereas, the selected column order is not important. It can be pasted in the last available column.

Hope i have answered your question.

Thank you and Kind regards.

**Doc.AElstein** · 08-30-2015, 05:54 AM

Originally Posted by shido

.......
I would like to keep the Naming Order, whereas, the selected column order is not important. It can be pasted in the last available column. .....

Ok.
. I think I get it all now.
. I will post when I have a solution ( should be Today or Tomorrow )
.
Alan

**shido** · 08-31-2015, 04:41 AM

Thanks Alan.

Looking forward to it.

Kind Regards,

**Doc.AElstein** · 08-31-2015, 06:57 AM

Hi shido,
. I will be working shortly on a solution for you. You may wish to consider something in the meantime

Originally Posted by shido

....
I would like to keep the Naming Order......

. When we are dealing with actual names there will be no “Number” to use to maintain any ordering.
. So it is unclear as to wot is defined as the “Name Order”. I will assume I bring a Row in at the point going down in the Sheet where no match is first found in names looking down in that column. Any other logic i think could error , for example, bring it in to the “row” corresponding to the row in that sheet from where it “comes” could leave empty rows in some cases in the receiving sheet. I expect coincidentally I will achieve my logic the actual results with your actual test data, but that may not always be the case ( I think? ) with other more jumbled data.. but I could be wrong: -. It a difficult point to grasp. I suggest you let it run through your mind, and reply back if you have any comment s to that in the meantime..
Alan

**shido** · 08-31-2015, 08:25 AM

Hi Alan,

Sorry for the confusion. Naming order would entail mixup of numbers and text.

For example: the list would start with QS001, QS002, .... to QS072. After QS0072, there may be some text such as manual handling, GDP, GMP.

I would say anything with text can be added in the next available row, whereas, anything with number would require order to be maintained, if possible.

Hope i have answer your concern.

Thank you and Kind Regards,

**Doc.AElstein** · 08-31-2015, 08:36 AM

Originally Posted by shido

Hi Alan,
Sorry for the confusion. Naming order would entail mixup of numbers and text.
For example: the list would start with QS001, QS002, .... to QS072. After QS0072, there may be some text such as manual handling, GDP, GMP.......I would say anything with text can be added in the next available row, whereas, anything with number would require order to be maintained, if possible. .....,

.
.

. It may be, dependinng on how / when you input data that my logic may autometically get what you want.
.
. let us have a look later when I have a code................
......................................
.
. In the meantime, think again about some test data ( still limited in size please to under 10 rows, if possible - if not then the maximum you need ) that would realistically reflect the possible combinations of those text and numbers

Alan

**shido** · 08-31-2015, 09:49 AM

Hi Alan,

thanks and Looking forward to the solution.

Kind Regards,

**Doc.AElstein** · 09-01-2015, 06:16 AM

Hi shido,
. This was a bit more tricky than I first thought..
. - you have some very complicated requirements. But I think I am close to a solution and will post when I am finished.
. Some points to be going on with:
.
. 1) Due to the complexity.. I took the unusual step ( for me ) of using Functions to tidy the code up.
Otherwise the code would have been unmanageably long. So I have written some Custom Functions. ( And may need a couple more, when I think about it... )
. I am not sure of what your knowledge level of VBA is , but if you are not familiar with VBA Functions ___() and Public Functions ___(), then a bit of background readings on the basic ideas there would be wise.
.
. 2 ) .....Your last clear instructions and example Before and After Sheets were essential,., - your original Description of the Problem gave no indication of the complexity!!! The code you gave in Post#1 is a long way off., - it is nowhere near anything like something to cope with your complicated requirements!
. ... You may wish to consider renaming your Thread title to something Like:
“....Based On selection: Copying , Adjusting, Reordering Rows and Columns from Tables, then Pasting into different sized Tables in specific order...”
.. it is a bit of a mouthful, but it may be helpful for anyone else looking for such a solution., or considering giving an alternative solution to mine.

. Alan

p.s. You might want to get yourself a crate of Guinness or similar for when I post my solution: The solution is necessarily a bit long and involved, so it is going to take a bit of concentration to follow...

**Doc.AElstein** · 09-01-2015, 08:02 PM

After:

Using Excel 2007

_Row\^Col	A	B	C	D	E	F
1
2	Name List	Designation 1	Designation 2	Designation 3	Designation 4	Designation 5
3	Name 1	1	11	21	31	41
4	Name 2	2	12	22	32	42
5	Name 3	3	13	23	33	43
6	Name 4	Nufink	Nufink	Nufink	Nufink	Nufink
7	Name 5	5	15	25	35	45
8	Name 6	6	16	26	36	46
9	Name 7	7	17	27	37	47
10	Name 8	8	18	28	38	48
11	Name 9	9	19	29	39	49
12	Name 10	10	20	30	40	50
13

Master List

Using Excel 2007

_Row\^Col	A	B	C	D	E	F	G	H
1		Awaiting	Move to Master List			Remove
2	Name List	Designation 6	Designation 7	Designation 8	Designation 9	Designation 10	Designation 11	Designation 12
3	Name 1	51	1	11	21	31	41	51
4	Name 2	52	2	12	22	32	42	52
5	Name 3	53	3	13	23	33	43	53
6	Name 4	Nufink	4	14	24	34	44	54
7	Name 5	55	Nufink	Nufink	Nufink	Nufink	Nufink	Nufink
8	Name 6	56	6	16	26	36	46	56
9	Name 7	57	7	17	27	37	47	57
10	Name 8	58	8	18	28	38	48	58
11	Name 9	59	Nufink	Nufink	Nufink	Nufink	Nufink	Nufink
12	Name 10	60	10	20	30	40	50	60
13

Awaiting

**shido** · 09-02-2015, 04:11 AM

Hi Alan,

Thank you very much and i really appreciate your time and effort. I thought it is going to be a simple change but the amount of work you did, its alot and i do not have words to say Thank you.

However, i followed your detailed and easy to understand instruction. It did work as intended. But when i went to select second column in "Master List" it was giving an error.

May be its some typo error in defining sheet or range name. let me please debug the code as you advised but first, let me make few cup of coffees

PS. My knowledge of VBA is basic and i am trying to learn it using Excel forum and google.

Once again, thank you Alan.

**Doc.AElstein** · 09-02-2015, 05:00 AM

Originally Posted by shido

...... But when i went to select second column in "Master List" it was giving an error........#
.... let me please debug the code as you advised but first, let me make few cup of coffees

.....

.. Ok try the De Bug thing. Then let me know how you get on.
. And tell me then again clearly what you are doing that cause the errror.
. I have not extensively checked the code. As you see it is quite a complicated code.
. So good to take it step by step
.
.

.

Originally Posted by shido

..... I thought it is going to be a simple change ........

. what really complicates things is the differen size / length of the columns you are wanting to move about, and the extra order requirement. That makes it an unusual and interestint, if techinically challenging one..

**Doc.AElstein** · 09-02-2015, 05:47 AM

Originally Posted by shido

..... But when i went to select second column in "Master List" it was giving an error......

Hi shido,
. I think I see what could be the problem if after the initial run you make a selection: - The problem I think is that I stupidly have not allowed in the code for the case when no rows need to be copied in this sheet or that sheet.
. with hind sight an obvious requirement, and a profie would probably have allowed for that instinctively – ( BTW. my VBA knowledge is similar to yours and similarly I am learning from Google and Books and my experience here. For example Drop down boxes are new to me, and look a nice idea. I was not sure how my code would cope with them, but thankfully it appears VBA is only interested in what is in the cell, and whether you type in or select with the drop down box VBA reacts the same, which was a relief! . You will note I specifically use .ClearContents rather than .Clear – I hope that means my code just empties the cell value and never takes out your Drop Down boxes – which I would not know how to put back in. )

.... I will take a look at that now, and let you know what I come up with. It is easy technically to fix, but is just a case of trying to think of a solution that avoid making the program even more complicated.
. ( BTW , if the explain green comments are annoying in the code, as I mentioned finally they can be easily knocked out in one go... for example from about Post #7, Post #10 here:
http://www.mrexcel.com/forum/about-b...ents-code.html

Alan

**Doc.AElstein** · 09-01-2015, 08:00 PM

Hi shido,
.. here it comes ( first instalment.) Make a coffee or open the first bottle of Guinness

. I suggest as this is a bit involved and I do not know your level of VBA we should take this one bit at a time. There is however enough info here to get the final complete solution, if you are able.
.
. The basic solution has 3 main codes, which are identical but work on each sheet and spring into life when you make your drop box selection in any sheet. In addition some Public function Codes are needed as I mentioned in Post #15.
.
. I will need to split this into more than one post because of Post size limitations.
.
. In this Post, #16, some general points.
.
. In the next post, Post # 17 I will walk through what to do to meet your first Descriptio from post #6
...
...”.................... 1. Master List: I would like to move “Designation 06” from “Master List” to “Awaiting” worksheet via selecting it from dropdown.
a. To move “Designation 06”, Name list shall be compared at first. Comparing “Name list” in both sheet, we found that “Master List” is missing “Name 4”, whereas, “Awaiting” is missing “Name 5” and “Name 9”.
b. At First, add missing name i.e. “Name 4” in the “Master list”.
c. Secondly, add “Name 5” and “Name 9” in the “Awaiting”
d. Once all names matches in both sheets, Move “Designation 06” from “Master List” to the “Awaiting”.
.................”

.............................

. I will be actually doing and following my instructions I give in Post # 17, working in your Supplied “Before” sheet to get the results on the “After”, but only as far as the bit above. – That is to say I am demonstrating what happens when you make a drop down box selection in sheet “Master List”.
. I will aim at a point of basic VBA understanding. But I will be happy to go into more detail if you wish.
.
. ( The actual codes I will “Farm off” to a spare Post in a Spare Thread and reference that rather than posting it here so as to make these posts a bit less cluttered. )
............................

General Notes

. 1) I try to do codes using VBA Array as much as possible just now as that is what I am trying to learn.
. If you know how, it is worth setting a watch on the various Arrays and then running the code in debug ( F8 ) Mode the first few times you run it.. This helps make clear what is going on, and much easier to see if / when any problems come in

. 2 ) It is important That All Names , headings etc. are spelt exactly the same in all sheets ( I had to correct, for example,
„List“
in sheets
„Awaiiting“ and „remove“
To read
„Name List“
in order to match the corresponding heading in Sheet „Master List“ )

. 3 ) I think the code currently, for convenience, relies on the Three Sheets “Master List” , “Awaiting” , and “Remove” being sheet item Numbers 1 , 2 and 3 . – That means they are the first 3 Tabs you see looking from the left

. 4 ) There is a lot of extra stuff, unnecessary lines and loads of comments – i need them to remind myself of what is going on . We can easily tidy up all that later when you are happy with the code.
. 4a) Also for initial simplicity of getting the code and code idea across to you I made the Main code such that the same one should be able to used in each sheet, ( as a Main code is required in all sheet Modules in addition to the Public Functions Module , - as i already mentioned..)

. 5 ) I got a bit confused with the terms source and destination, as that only refers to the columns you send. In addition your are copying and pasting rows back and forth. So I went over to using the terms “This” and “That”
.
. “This” is the sheet you are in when you make the drop down box selection

. “That” is the sheet you select in the drop down box, ( and where the program puts the chopped out column )
.
. 6 ) With your current test data the rows come out in you wanted order. We may need to think a bit about that when we have some more representable data, as I discussed in post # 13
.
. 7 ) You said the selected column order is not important. And that it could be pasted in the last available column. But I was feeling a bit anarchistic so I arranged that it would be put in the second column with all other columns being shifted across to the right. That is very easy to change.
.
. 8) The nice thing about Excel Forum is that I can edit my posts for a long time . So usually when I feel in the mood I come back and correct typos, make minor improvements, remove vulgarities etc.. from my codes. So it is always worth checking for a fresh version in the future should you be using any of my codes
...........................

**Doc.AElstein** · 09-01-2015, 08:00 PM

. So here we go again:

. 1) Open a copy of the before sheet you provided me with

. 2) copy all of the following codes in one go to the Clipboard. They are the Functions called by the Main Code as necessary
Posts #4 and posts # 5 and post #6
http://www.excelforum.com/developmen...ml#post4178850
http://www.excelforum.com/developmen...ml#post4178853
http://www.excelforum.com/developmen...ml#post4178855
. 3) These 7 above codes can go into any macro module. I would usually make a separate module for them called something like PubicFuktions, but that is how I am. Remember if you are copying into an existing module, then the Option Explicit bit must be written just once at the start of the module
.
. 4) Make the correction to „Name List“ as noted in last post # 16
.
. 5) copy this main code to the clipboard and paste in THE SHEET MODULE, NOT a normal module. ( Sheet "Master List" )
NOTE I HAD TO PASTE IT INTO TWO posts, but it is all one code, paste the second part immediately after the first in the sheet Module
Posts # 7 and Posts #8
http://www.excelforum.com/developmen...ml#post4178861
http://www.excelforum.com/developmen...ml#post4178864

. 6 ) Save the file, close the file, and reopen it, remembering to select enabling macros

. 7a ) If you know how, put a stop in the left hand margin somewhere near the start of the code. Make the “Awaiting” Selection as per your example from post # 6. Step through the code with F8 after setting watches on various Arrays
.
. 7b) Try your luck at just running the code normally by making your selection. Remember the results at this stage are just those based on that one selection.

............................
. Anyway have a play, see how you get on, and when you report back we will take it from there if / as need be

Good luck.
Alan

P.s. Results in the next 2 Posts post are what I had before from you, and after I ran my code through making the above selection:

**Doc.AElstein** · 09-01-2015, 08:01 PM

Before:

Using Excel 2007

_Row\^Col	A	B	C	D	E	F	G
1							Awaiting
2	Name List	Designation 1	Designation 2	Designation 3	Designation 4	Designation 5	Designation 6
3	Name 1	1	11	21	31	41	51
4	Name 2	2	12	22	32	42	52
5	Name 3	3	13	23	33	43	53
6	Name 5	5	15	25	35	45	55
7	Name 6	6	16	26	36	46	56
8	Name 7	7	17	27	37	47	57
9	Name 8	8	18	28	38	48	58
10	Name 9	9	19	29	39	49	59
11	Name 10	10	20	30	40	50	60
12

Master List

Using Excel 2007

_Row\^Col	A	B	C	D	E	F	G
1		Move to Master List			Remove
2	List	Designation 7	Designation 8	Designation 9	Designation 10	Designation 11	Designation 12
3	Name 1	1	11	21	31	41	51
4	Name 2	2	12	22	32	42	52
5	Name 3	3	13	23	33	43	53
6	Name 4	4	14	24	34	44	54
7	Name 6	6	16	26	36	46	56
8	Name 7	7	17	27	37	47	57
9	Name 8	8	18	28	38	48	58
10	Name 10	10	20	30	40	50	60
11
12

Awaiting

**shido** · 09-07-2015, 05:46 AM

Hi Alan,

Sorry for coming back to you late.

There is still a minor issue with the code. I changed column A similar in all sheet and test the code.

1. Move column from Master list & Awaiting to Remove - No issue found

2. However, when i move column back from awaiting or remove to master list &/or from remove to awaiting, It is giving some issues,
2a. deleting row 1:1 as its seen in the attachment.

2b. after deleting row 1:1, it is then pasting selected column from remove or awaiting sheet as shown in the attachment.

ad.jpg

Kind regards,[

ATTACH]417705[/ATTACH]

**Doc.AElstein** · 09-10-2015, 04:44 PM

Hi,
. a bit of further input here:- out of interest I took a quick look back here...

. As I said i am learning by doing these Threads and this was a Big Donkey of a code for me, multiple width row, and sheets etc..
. FWIW, I guess I have confirmed that it was a good idea to take the implementation step by step..
. Going from Our Post 24 25 point ... and looking at sheet Awaiting . Assume in the last column , I, in cell I1 ( dropdown box ) I select Move to Master Sheet....

Using Excel 2007

_Row\^Col	A	B	C	D	E	F	G	H	I
1		Awaiting	Awaiting	Move to Master List			Remove
2	Name List	Designation 1	Designation 6	Designation 7	Designation 8	Designation 9	Designation 10	Designation 11	Designation 12
3	Name 1	1	51	1	11	21	31	41	51
4	Name 2	2	52	2	12	22	32	42	52
5	Name 3	3	53	3	13	23	33	43	53
6	Name 4	Nufink	Nufink	4	14	24	34	44	54
7	Name 5	5	55	Nufink	Nufink	Nufink	Nufink	Nufink	Nufink
8	Name 6	6	56	6	16	26	36	46	56
9	Name 7	7	57	7	17	27	37	47	57
10	Name 8	8	58	8	18	28	38	48	58
11	Name 9	9	59	Nufink	Nufink	Nufink	Nufink	Nufink	Nufink
12	Name 10	10	60	10	20	30	40	50	60
13

Awaiting

... so I copied the Worksheet_change code to Awaiting sheet Code Module. But importantly at this stage deleted the Worksheet_change code in sheet Master List ( more to that later!!*** )

. So plan is as, an example, Assume in the last column , I, in cell I1 ( dropdown box ) I select Move to Master Sheet....

.. A point that immediate struck me is that in Awaiting column H and I have no check box In!!! So that didn’t work!!!
... obvious Point – as columns were shifted across no account is taken of that.
. copying ( manually for now ) across reveals a further obvious point*** - Any change anywhere sets off the Worksheet-change Program and or programs!!***

... But for now .. I take the above and select Move To Master List in cell I1.. ...
. Follow through F8 Debug... then “suck it and see”

. - Problem 1)
. ... Typo:....( Mine )
Move To Master
is wrong in code: should be
Move to Master List
.....
....
. - Problem 2)
We use the CurrentRegion Property, which Applied to a range Object returns a Range of the "box" which extends to include all cells connected to That Range. Currently Master list has nothing in first row, which of course can happen, ! so CurrentRegion Property does not catch first row!!
. - Ok for now put in all sheets in cell (1, 1 ) Anything:
Using Excel 2007

_Row\^Col	A	B
1	AnyThink
2	Name List	Designation 2
3	Name 1	11

Master List

..............
Now the complete running of the program gives me this ( partial screen shots )

Using Excel 2007

_Row\^Col	H	I
1	Remove
2	Designation 11
3	41
4	42
5	43
6	44
7	Nufink
8	46
9	47
10	48
11	Nufink
12	50

Awaiting

.. column I is gone from Sheet Awaiting as expected, and ................
Using Excel 2007

_Row\^Col	A	B	C	D
1	AnyThink	Move to Master List
2	Name List	Designation 12	Designation 2	Designation 3
3	Name 1	51	11	21
4	Name 2	52	12	22
5	Name 3	53	13	23
6	Name 4	54	Nufink	Nufink
7	Name 5	Nufink	15	25
8	Name 6	56	16	26
9	Name 7	57	17	27
10	Name 8	58	18	28
11	Name 9	Nufink	19	29
12	Name 10	60	20	30

Master List

... is squeezed in at the second column in Sheet Masta List

.... I think this is getting there.. I was actually expecting more difficult problems: These couple are just carelessness / lack of experience on my behalf. I think my main concern was with some possible sorting and order requirements with actual data..
... but I will leave it at that stage for now, pending any feedback or my next urge to tidy up / recheck further the code.

Alan..

**Doc.AElstein** · 09-07-2015, 11:07 AM

Hi shido,
. good to hear from you, I had thought the complexity of the code may have scared you off using it further!!!
………………………………………..

. 1 )
. I may have ”lost the thread” as it were in my head in the meantime on what was quite a complicated code. I shall try to “get it back up” in my head if and when I have the time and try to help further. You appreciate for such a code I may need a bit of time to get it current in my head again.
. In the meantime…

… The last I can remember, as reflected by the last post from me in the Thread, is that we were at the point given from Posts #23 to # 24 .
.
. To help me please take me very carefully step by step , with screenshots if you can, of exactly what you then did from there and where the problem then comes.

…………………………………………………………
. 2 )

Originally Posted by shido

.......
2. However, when i move column back from awaiting or remove to master list &/or from remove to awaiting, It is giving some issues,.......

.. this suggests ( I think ) you have tried putting the same code in the other two sheets. This is the correct further progression, but in the thread I had not got that far. So please tell me if / how / what you did in this respect, and maybe upload your current file. ( As it was such a complicated code I suggested we take it step by step. You have “bravely” jumped ahead. All credit to you for having a go. But it does mean I have now to “catch up” a bit. ( I am glad I made good clear instructions in the Thread : I fear I may need them myself now to remake the File, as just now quickly I could not find it - It is almost a week now, and it sort of fell down the pile somewhere!!! )

Alan

EDIT: I see now you have attached a file. OK I will look at that but please supply the other info asked for, Thanks

**shido** · 09-12-2015, 05:55 PM

Hi Alan,

Thank for the follow up and your time.

I have been playing with the original code myself. I have decided to make Name list same in all sheet, just to get to the solution. I know my variable range causes alot of the problem and took alot of your time.

However, i have come to the following code and its working fine. only deviation i did is to make name list common.

Private Sub Worksheet_Change(ByVal Target As Range)

'http://www.ozgrid.com/forum/showthread.php?t=154637

    Dim strStatus As String

    Dim wsAwait As Worksheet

    Dim wsRmove As Worksheet

    Dim wsMList As Worksheet

    Dim lstrw As Long

    

    If Target.Column = 1 Then Exit Sub

    If Target.Row <> 1 Then Exit Sub

    If Target.Cells.Count > 1 Then Exit Sub

   

    Application.ScreenUpdating = False

    

      

    strStatus = Target.Value

    Set wsAwait = ThisWorkbook.Sheets("Awaiting")

    Set wsRmove = ThisWorkbook.Sheets("Remove")

    Set wsMList = ThisWorkbook.Sheets("Master List")

    lstrw = wsAwait.Rows.Count

    

    If strStatus <> "Move to Master List" Then

        Range(Target, Target.Offset(0, 2)).Copy

        Application.EnableEvents = False

        Select Case strStatus

            

        Case "Awaiting"

            'wsAwait.Range("A" & lstrw).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

            Columns(Target.Column).Copy wsAwait.Columns(wsAwait.Cells(2, Columns.Count).End(xlToLeft).Column + 1)

            

        Case "Remove"

            wsRmove.Range("A" & lstrw).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

            

        End Select

Thanks once again Alan for the direction and effort

**Doc.AElstein** · 09-13-2015, 05:24 PM

Hi

Originally Posted by shido

...... I have decided to make Name list same in all sheet, just to get to the solution. I know my variable range causes a lot of the problem .........
However, i have come to the following code and its working fine. only deviation i did is to make name list common........

. Hi thanks for coming back – it was a difficult one so good to know how it came out, Thanks.
. So basically you have “tidied up” or “restructured” the data to do away with all thet difficult chopping and changes of rows between Sheets with Ranges of different sites, along with some reordering of rows as you go along. – “Wow it is almost a mouthful just to say!!!”
. I guess it is always a bit of a “chicken and Egg” situation , not knowing which is the less of the evils, - making the data better structured with all the work that entails, or developing a more complicated code, which as well as the time taken can , as you see take a while to debug / check that it works finally.
. Happy Yous got there and that Yous may of least got some direction from the thread, so thanks for the feedback

Alan
.
P.s.
. I think now the codes are at or very close to working to meet your initial complicated criteria.
. So can I ask if you have the time to reconsider renaming the Thread Title. This could help navigate someone searching for a solution to such a complicated requirement should they for any reason have to take that route.
.
. I would suggest a title sof the form
“....Based On selection: Copying , Adjusting, Reordering Rows and Columns from Tables, then Pasting into different sized Tables in specific order...”... ( If you can fit that in!! )
FYO
. to do that :
. Go to your first Post , Post #1
. Hit Reply
Attachment 418734

. Scroll down and Hit Go Advanced
Attachment 418735

. Scroll up and you see you can Edit the Title
Attachment 418736

IMPORTANT : There is a Forum Bug. Your original Post can “vanish”. So before you start the above copy the entire contents of your first Post to the clipboard, so that if the worst occurs you can paste that post back in. ( Your text would probably be enough as that initial code you gave was not too relevant to the complicated criteria )

. Hope that is all clear. Thanks in advance if you can do that. But its your Thread - so your decision.
. Good luck with your project
Alan

**shido** · 09-14-2015, 05:33 PM

Title changed to reflect the post

**JBeaucaire** · 09-22-2015, 02:38 PM

To change the title of the thread, EDIT post #1, the GO ADVANCED.

Hybrid View

copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Re: copy entire column instead of rows

Based On selection: Copying , Adjusting, Reordering Rows & Columns from Tables, then Paste

Re: copy entire column instead of rows

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Copy Entire Column Only Returning 1st 16 Rows?

[SOLVED] Macro to Copy Entire Rows if Column Equals

Bookmarks

Bookmarks

Posting Permissions