Hi guys,
I would like some help with VBA/macro code. I need to copy undefined number of rows in one column to another worksheet.
In attachment there is an example how it should work, provided that the macro has to copy text on to another worksheet and number od rows is not known.
Copy ALL.xlsm
Thanks!
Your explanation is not clear and your example in your spreadsheet has no explanation. What do you mean by an undefined number of rows in one column. Please clarify. Explain your situation explicitly to me as if we were standing in line at grocery store waiting to check out.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Sorry for the bad explanation. When I said undefined number of rows, I thought it could be two rows data or fifty or hundred.
The data from the rows should be copied in one column on another worksheet.
Example:
A1 -> C1
B1 -> C2
C1 -> C3
A2 -> C4
B2 -> C5
C2 -> C6
A3 -> C7
B3 -> C8
C3 -> C9
...
better?
EDIT: EXAMPLE
SHEET1
DATA1 DATA2 DATA3
DATA4 DATA5 DATA6
DATA7 DATA8 DATA9
...
SHEET2
DATA1
DATA2
DATA3
DATA4
DATA5
DATA6
DATA7
DATA8
DATA9
...
Last edited by Rogy; 01-16-2015 at 05:31 AM.
Try this:
How to install your new code
Please Login or Register to view this content.
- Copy the Excel VBA code
- Select the workbook in which you want to store the Excel VBA code
- Press Alt+F11 to open the Visual Basic Editor
- Choose Insert > Module
- Edit > Paste the macro into the module that appeared
- Close the VBEditor
- Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
To run the Excel VBA code:
- Press Alt-F8 to open the macro list
- Select a macro in the list
- Click the Run button
alansidman, thank you!!! this work great! this will help me a lot!Originally Posted by alansidman
Try this:
How to install your new codePlease Login or Register to view this content.
- Copy the Excel VBA code
- Select the workbook in which you want to store the Excel VBA code
- Press Alt+F11 to open the Visual Basic Editor
- Choose Insert > Module
- Edit > Paste the macro into the module that appeared
- Close the VBEditor
- Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
To run the Excel VBA code:
- Press Alt-F8 to open the macro list
- Select a macro in the list
- Click the Run button
but if you could help me with two more things.
first, that the copying is done on sheet2 in column C not A, and not skipping the first row.
second, not to skip empty cells
EXAMPLE:
SHEET1
DATA1 DATA2 DATA3
DATA4 DATA5 DATA6
DATA7 DATA8 DATA9
DATA13 DATA14 DATA15
...
SHEET2
DATA1
DATA2
DATA3
DATA4
DATA5
DATA6
DATA7
DATA8
DATA9
DATA13
DATA14
DATA15
...
i hope i explained well, for me its very difficult to explain in English.
Last edited by Rogy; 01-16-2015 at 06:21 AM.
Hi Rodgy,
. Alan S already had you "Sort ed" as I was in the middle writing this....
.....so I finished it anyway as an alternative...
.. it is the typical "Array capture" type alternative method to Alan S#s "Spreadsheet" type method (Which i actually prefer as it is easier as a beginner to follow). But theoretically mine is faster for lots of data (I think?) - (It captures all data in one go, does all sorting in VBA as it were, then outputs end results in one go)
... here it is (and I modified it for your new requirements. ). (Try to get your requirements right first time as it is not always easy to modify and sometime means re- writing everything again (I was lucky here, and the modifications were easy)
Here then the code:
……. And just in case you want empty cells to be ignored, a second versionPlease Login or Register to view this content.
Please Login or Register to view this content.
…
Hope that helps.
Alan E
P.s. (By the way, I expect a formula could do this for you.. unfortunately I am not very good with those..)
Maybe:
Please Login or Register to view this content.
Alan E, it helps a lot! This work like a charmHi Rodgy,
. Alan S already had you "Sort ed" as I was in the middle writing this....
.....so I finished it anyway as an alternative...
.. it is the typical "Array capture" type alternative method to Alan S#s "Spreadsheet" type method (Which i actually prefer as it is easier as a beginner to follow). But theoretically mine is faster for lots of data (I think?) - (It captures all data in one go, does all sorting in VBA as it were, then outputs end results in one go)
... here it is (and I modified it for your new requirements. ). (Try to get your requirements right first time as it is not always easy to modify and sometime means re- writing everything again (I was lucky here, and the modifications were easy)
Here then the code:
……. And just in case you want empty cells to be ignored, a second versionPlease Login or Register to view this content.
Please Login or Register to view this content.
…
Hope that helps.
Alan E
P.s. (By the way, I expect a formula could do this for you.. unfortunately I am not very good with those..)
Thank you very much, and Alan S too. I admit that I was wrong in my explanations and requirements, I'll be better next time, more precise.
And comments in code are very helpful. Thank you for the time you set aside and your knowledge.
This works also, thank you John H!
I have to notice that this code is a lot shorter.
Hi JOHN H. DAVIS,
. The great things about these Forums is
. 1) seeing the ever increasing different ways of doing the same thing.. I find that the best way to learn
. 2) learning “new” stuff…
… I spent some time looking at detail at the .End property.. I never came up with your version…. And a quick google just now did not find it either….
… but a quick bit of experimenting sees that it is .End(The column number) (The increment in the last cell found by the .End Property).
A great new idea for me – going to the next range as it were with ( ) (2) rather than as usual going to the next row
Thanks
Alan Elston
Last edited by Doc.AElstein; 01-16-2015 at 08:11 AM. Reason: Typos
. Hi Rogy,Alan E, it helps a lot! This work like a charm
Thank you very much, and Alan S too. .........
And comments in code are very helpful. Thank you for the time you set aside and your knowledge.
You are Welcome. Glad we all could help.
. Alan
P.s. 1 Welcome to the board !!!
P.s. 2 Edit out some of the stuff in the quotes you give when you reply...(See how my Quotes from you look like)... - Do not include the entire quote unless you really need to, as it makes the Thread unecessary long and difficult to read easilly
P.s. 3 Glad you liked the comments! . I love them......Most people find them annoying!!
Last edited by Doc.AElstein; 01-16-2015 at 08:17 AM.
Alan:
I love to learn new things in the Forums as well, but just to clarify the 3 in .End(3) does not represent the column number, but a shorter version of .End(xlUp).
...thanks I probably would of noticed that.... the next time when I used it and it went wrong!!!!!!
. so thanks for saving me a bit of headache
Alan
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks