Non-contiguous copy paste into single row

    I need help with an efficient method for taking my current selection of non-contiguous cells, then pasting onto another sheet all into one row. I attached a simplified version of what I'd actually apply this to. Each row I want to copy Column A through the column before the Date starts (which could be the same but will mostly change). Then I want to be able to copy that and paste it all to be in order in one row (on another sheet if that's necessary to know). I have not had the time to learn VBA, but am trying to make the time for it. Pretty much all VBA I've used has been found online and modified slightly for my needs. I tried looking all over this forum and couldn't find something close enough.


    This is my first time posting here, so please just let me know if I can clarify or help in any other way.

    I Don't know what you mean by this: "then pasting onto another sheet all into one row."

    The 2nd macro will paste everything into one row if that is what you want.

    Try both of them but clear the Transfer sheet after using one and before using the next code

    Both are based on what you supplied.

    Code
    Sub Maybe_So()
Dim i As Long
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(i, 1), Cells(i, Rows(i).SpecialCells(2, 2).Cells(1).Column - 1)).Copy Sheets("Transfer").Cells(Rows.Count, 1).End(xlUp).Offset(1)
Next i
End Sub
    Code
    Sub Or_Maybe_This()
Dim i As Long
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(i, 1), Cells(i, Rows(i).SpecialCells(2, 2).Cells(1).Column - 1)).Copy Sheets("Transfer").Cells(1, Sheets("Transfer").Columns.Count).End(xlToLeft).Offset(, 1)
Next i
End Sub

    This code will copy the selected cells to the Transfer sheet. The code will ask for a destination, type in say A2 in the InputBox.


    You could replace the inputbox by allowing the code to automatically copy to the next empty row in a table of data.

    Quote from jolivanes

    I Don't know what you mean by this: "then pasting onto another sheet all into one row."

    The 2nd macro will paste everything into one row if that is what you want.

    Try both of them but clear the Transfer sheet after using one and before using the next code

    Both are based on what you supplied

    First of all, thank you so much!


    Your second macro did gave me what I was looking for, everything pasted into one row. This worked in the workbook I provided. I did have a problem converting it to my original file. So basically on the raw data sheet, what I provided would be raw data from one source which is copy/pasted from a text file. In the file i'm intending this for, I have many data sources, I usually just leave a handful of blank rows in between. But I still only want to transfer the data over one source at a time, then i'll clear the data out of "Transfer" and can transfer the next data source. The code didn't appear to like having the extra rows of blanks (my guess).


    Another change from the file I provided versus the one i'm applying this code to. I actually have some more data that I removed for simplification. There are 3 additional columns to the left that I do not need copied over (but are needed to be in the raw data). There is also 2 additional rows (again necessary to be in the raw data, but not to be copied over). So previously I just provided what I would like copied over.


    At a minimum, I can definitely use what you sent me if these other things can't be worked around. I'm trying to understand exactly what is going on with each line but it's hard without more training (trying to get official approval from work to get training so it doesn't eat up all my free time).


    Attached is more representative of my intended spreadsheet and some explanationsNon-Contiguous CopyPaste_V2.xlsmNon-Contiguous CopyPaste_V2.xlsm. Sorry I can't just provide my intended document, it's for security purposes.

    Quote from royUK

    This code will copy the selected cells to the Transfer sheet. The code will ask for a destination, type in say A2 in the InputBox.


    You could replace the inputbox by allowing the code to automatically copy to the next empty row in a table of data.

    I'm sorry, maybe i'm missing something. But the file I grab from your post seems to by my original document, I don't see any code in it.

    Quote from jolivanes

    I Don't know what you mean by this: "then pasting onto another sheet all into one row."

    The 2nd macro will paste everything into one row if that is what you want.

    Try both of them but clear the Transfer sheet after using one and before using the next code

    Both are based on what you supplied

    I actually just realized this isn't working as intended when it's pasting. In the 2nd file I attached I explain that all the numbers being transferred are XY coordinate pairs. They are not being kept together, which greatly affects the outcome of the data. It also should have had a total of 53 pairs, but only 49 were output.

    Quote from royUK

    There should be a sub CopyCells in the workbook

    Can you please try posting again? I re-opened it and can't find anything. I looked in Macros, then opened the VBA window and check under every possible sheet. I hope i'm not making some massive rookie mistake

    It looks like you better attach a workbook with a before, which these have, and an after, which these don't have. I guess you have to do that manually.

    An concise explanation on how you arrived at the result would be needed also.

    Quote from royUK

    Try this

    Perfect! This is exactly what I was asking for! I had found something very similar by googling, but it was not pasting it into one row and am not knowledgeable enough to modify much.


    One last modification if I could ask (I actually have something dealing with the date/time cells I could ask but i'll at least attempt some solutions first). I will always place into cell A2 on the Transfer Sheet, how could I modify the code to do that? I know I could just type in A2 every time, but I love to remove unnecessary steps.

    Quote from jolivanes

    It looks like you better attach a workbook with a before, which these have, and an after, which these don't have. I guess you have to do that manually.

    An concise explanation on how you arrived at the result would be needed also.

    That would have been a better way, thanks for the suggestion. Give me some time (maybe an hour or two), i'm going to incorporate the code from RoyUk. Then i'll post a before and after, maybe some of my minor tweaks can be answered then.

    Is this what you mean?


    Quote from jolivanes

    It looks like you better attach a workbook with a before, which these have, and an after, which these don't have. I guess you have to do that manually.

    An concise explanation on how you arrived at the result would be needed also.

    I technically have everything I originally asked for. But attached also includes my final product that i'll copy and paste to another sheet not on this document. Any input on how i can use VBA to make this an easier process is very welcome and appreciated. What's already been done is very helpful. But if this could become even more automated to include what I mention in the file I would be very ecstatic. This is valuable to me professionally and would be extremely grateful.


    The code that RoyUK provided is used for copying and pasting my current selection over to the Transfer Sheet.

    Quote from royUK

    Try this

    Originally by request of Jolivanes, but maybe you'd like to help too. I technically have everything I originally asked for. But attached also includes my final product that i'll copy and paste to another sheet not in this document. Any input on how i can use VBA to make this an easier process is very welcome and appreciated. What's already been done is very helpful.

    Try this.

    It is based on your attachment that has the data in the "Raw Data" Sheet starting in Range B17 and ends at B28 (I think it is Version 3)

    The result starts at Cell A6 in "Transfer" Sheet.

    Macro to run when "Raw Data" Sheet is the Active Sheet (showing on the desktop).

    Wouldn't be a bad idea to try on a copy of your origiginal first and check the result thouroughly.

    Looking at it again, you might want to replace

    Code
    .Offset(, 1).Value = Left(a, 9) & Mid(a, 11, 5)

    with

    Code
    .Offset(, 1).Value = Application.Replace(Mid(a, InStrRev(a, ":") + 1), InStrRev(Mid(a, InStrRev(a, ":") + 1), "."), 1, "")

    to avoid possible wrong counts, 9 and 11 in above line.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login