Cut and paste VBA

[HuskerBronco]

Hi guys. Me again.....

I'm new to VBA...(just started learning last year)...and everything that I learn, I learn mostly by trial and error...referring to a handful of VBA sourcebooks... and from the EXPERT advice and guidance I receive from this forum. There hasn't been a situation, yet, that you people haven't been able to resolve with brilliant suggestions and code construction.

MY problem this time, is constructing VBA code for a Macro that I recorded with the Macro Recorder. Clearly, the Macro is NOT the most efficient way to accomplish the task that I need to perform...but I can barely begin to understand exactly how to construct VBA to perform this task. Here is the task:

Suppose Sheet1!B1:??1 contain unique entries. (The [??] indicates that the last column with entries is unknown).
Directly below that...[Sheet!B2:??2]..is another range of unique entries.

What I need to do, is cut a pair of entries from each COLUMN, and paste them into one single column (A:A)...with a single row separating each pair of entries...like so:

Item from B1
Item from B2
(Empty row)
Item from C1
Item from C2
(Empty row)
Item from D1
Item from D2
(Empty row)...etc.

Simple enough...right?? (for you guys...!!!)
I know I need to set DIM for multiple cut/paste operations...but aside from that...I don't how to continue.

As usual...thanks, in advance, for your excellent suggestions.
Each time you guys (and gals) help me out...I gain a little more insight into exactly HOW and WHY VBA works.

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[HuskerBronco]

Hi royUK...

Here is the sample worksheet you suggested.

[Andy Pope]

Something like this do.

Please Login or Register  to view this content.

[HuskerBronco]

That worked perfectly...Andy!!!
And...I understand the purpose of each part of your code.
Thank you for your quick, useful response.

[HuskerBronco]

Whoops....!!!

Andy's code worked perfect until I tried to run it UNDERNEATH some cells that are populated with an array formula.

Then it kept telling me that I "couldn't change part of an array".

I even changed the parameters of the code so that lngRow was set at 11...well below the array cells.

Am I doing something wrong...???

[Andy Pope]

I assume then that the formula in the first 2 rows actually contains an array formula spanning either more than 2 rows or more than 1 column.

Does it make sense to copy the cell in this case? Should you not just copy the values.

[HuskerBronco]

Andy...

Here's what I did...

Instead of running your VBA on a NEW worksheet, I tried to run it on an existing worksheet.
Attached is a condensed version of this worksheet.

I was hoping to have the new range appear in the shaded cells.
As you can see...those shaded cells ARE below an array.

I adjusted your VBA as follows:

1. Changed rngData start range from "B1" to "B9"
2. I also changed lngRow from "1" to "9"

I thought that would start the NEW range in cell A9...but, obviuosly...it doesn't...because it tries to change part of my array.

Is there any way your VBA can be written to "skip" the cells containing the array?

I have an entire worksheet that contains nothing but data for each week.
The TOP 7 rows of this worksheet are populated...most of the way across...with arrays.
Every week, I would like this new range to appear on this DATA worksheet.

This is not a necessity...but, my other option is to create a separate worksheet strictly for this purpose.
I'd like to avoid that. Any suggestions??

[Andy Pope]

Please Login or Register  to view this content.

[HuskerBronco]

Ahhhhh.....!!!!

I changed the wrong parameter....!!!!

Just when I thought I knew what I was doing...!!!

Thanks, a bunch, Andy...!!!