Copy & paste again

hi

my following code will not work as I do not know how to phrase the first 3 lines in the correct syntax) but that's just what I'm trying to achieve: to copy several ranges in different columns simultaneously instead of separately, then PasteSpecial their values to another workbook at the starting at the target EZ2 mentioned all columns standing side-by-side to each other in that sequence.

Can anyone point the way ?

Please Login or Register  to view this content.

Stewart Mann

hi rollis13

i've tried your Union method, but it's not picking up the rest of the rows with data, it's only copying the top row (row 2) data. And another problem is that the row2 pasted is not copied according to the macro specified sequence, it is copying according to the alphabetical sequence.

I also tried xlUp but same problem only row2 was pasted.

Wonder what's causing this

Stewart

.. but the original code by rollis was already qualified with wbSoun, i dont think that was the cause. Besides, if the issue was that, it wouldn't even pick up row2 at all, but it does only row2 oddly.

Anyway, even if that could be solved, i'm more discouraged by the fact it won't copy according to the sequence specified and just goes alphabetical order. That would make this method totally redundant and i'll have to go back to separately copy and paste 1 by line tediously like before

Maybe with these changes it would work better, I have no way to test it on real data.

Please Login or Register  to view this content.

Thanks to @ByteMarks for pointing out a possible problem.

rollis13

ok, that works, but the other issue remains, sequence not as specified in macro, Excel has pasted it alphabetically. is there any way to force the sequence according to macro ? It's now pasted in this seq:

B - G, J, AD - AF

Stewart

Ok, lets revert, this version will copy/paste in the right order:

Please Login or Register  to view this content.

MannStewart, try below code ...

Please Login or Register  to view this content.

@Rollis13
appreciate your suggestion, however considering the length of this code, it defeats my initial intention to accomplish the task in a compact 1 line or at least as simple as Union. I was already pasting them line by line in 3 pairs like what you did in between With, End with without needing to declare an additional 'lc', in my needed sequence by an inefficient separate copying & pasting, but for lack of a better solution, i'll just have to get back to square one for now

cheers
Stewart

@nankw83

Your code did it, this is what i'm talking about .. it's brilliant

In the actual code I actually had a clearcontents line preceding the body of the code, but i only just realised it's not working when i tried to paste the new data, as i noticed it's only clearing the row2 every time, i hope you could also point out what's wrong with it:

Please Login or Register  to view this content.

Stewart

Originally Posted by MannStewart

@nankw83 .. i hope you could also point out what's wrong with it:

Maybe ...

Please Login or Register  to view this content.

Or you could add the below line in my code provided earlier

Please Login or Register  to view this content.

got it ,
but i'm just wondering why putting the ClearContents line between COPY & PASTE process did not create a disconnect for the COPIED data.

So, if i have 2 sets of COPY & PASTE, with the second set in a different sheet, can I modify the code as follows? both with ClearContents per your method, but redeclared separately

Kindly correct me if i'm wrong anywhere in the code:

Please Login or Register  to view this content.

Stewart

Originally Posted by MannStewart

i'm just wondering why putting the ClearContents line between COPY & PASTE process did not create a disconnect for the COPIED data.

I don't know if I understood you correctly but in my code, I am not doing copy/paste like a range, I am assigning a data to an excel variable (a) then putting certain columns of this variable into the sheet. I don't have a file to test your code but it looks like it should work fine

@crusader2100 , welcome to the forum

Just a small tip, you can subscribe to any thread from the "Thread Tools" above the first post where you'll be able to track it & receive notification(s) on any updates

@nankw83

i see, so if Excel is not copying but assigning, why is the CutCopyMode = False line afterwards required? So for every set of assigning to do, i still need to add this Application CutCopyMode = False immediately afterwards ?


2. Mainly, i need to double check to ask about this line, a third copied range starts at A5 in the source sheet:
if i add a third copy range in another sheet where the copied range starts at GY5, must the variable 'a' still, and always start at column A? :

Please Login or Register  to view this content.

as long as Lr always refers to the last column number.

Did i understand correctly?


3. My last question is, what is the (3) inside the End command, is that a non-negotiable constant ?

Please Login or Register  to view this content.

Stewart

Originally Posted by MannStewart

so if Excel is not copying but assigning, why is the CutCopyMode = False line afterwards required? So for every set of assigning to do, i still need to add this Application CutCopyMode = False immediately afterwards ?

In my code, I'm not using copy/paste nor did I have CutCopyMode = False line. However, you can use copy/paste in VBA & use that line

if i add a third copy range in another sheet where the copied range starts at GY5, must the variable 'a' still, and always start at column A?

No, it depends on the structure of your file & where the data is located that you want to assign to variable 'a'

as long as Lr always refers to the last column number. Did i understand correctly?

No, Lr refers to Last Row not Column

what is the (3) inside the End command, is that a non-negotiable constant ?

This is just another way to write End(XlUp)

Glad to help & thanks for the Rep+