copy multiple rows to new single row sequentially in new worksheet

[jetpack]

excel 2016

I have 40+ columns of data in 100s of rows.

At a specified time each day I need to copy each row sequentially to a single row in a new worksheet.

As an example:

DataSheet is the sheet from which data should be copied. NewSheet is the receiving sheet.

Process would be as follows;

copy DataSheet A1:A40 to NewSheet A1 and add 1 blank column at end.
copy DataSheet B1:B40 to NewSheet A42 and add 1 blank column at end.
copy DataSheet C1:C40 to NewSheet A:84 and add 1 blamk column at end.
copy DataSheet D1:D40 to NewSheet A:125 and add 1 blank column at end.
Continue to end of rows.

# of columns and rows may change from day to day, but will always be the same # of each on any particular sheet. NewSheet would be named according to date procedure is ran, ie; 1_23_18 for Jan 23 2018.

Seems like a combination of loops and count(), but beyond my ability to construct anything that works.

Appreciate everyone who takes the time to read this post!

 

[My Aswer Is This]

Try this:

Sub Copy_My_Data()
'Modified  11/30/2018  5:56:59 PM  EST
Application.ScreenUpdating = False
On Error GoTo M
Dim i As Long
Dim ans As String
Dim Lastrow As Long
Dim Lastcc As Long
Dim LastColumn As Long
Sheets.Add(After:=Sheets(Sheets.Count)).Name = Format(Date, "mmm dd yyyy")
ans = ActiveSheet.Name
Sheets(1).Activate
LastColumn = Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To LastColumn
    Lastrow = Sheets(1).Cells(Rows.Count, i).End(xlUp).Row
    Lastcc = Sheets(ans).Cells(1, Columns.Count).End(xlToLeft).Column + 2
        If i = 1 Then Lastcc = 1
    Sheets(1).Cells(1, i).Resize(Lastrow).Copy
    Sheets(ans).Cells(1, Lastcc).PasteSpecial xlPasteValues, Transpose:=True
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "The sheet named  " & Date & " Already exist. I have stopped the script"

End Sub

 

[My Aswer Is This]

Your original post said:

At a specified time each day I need to copy each row sequentially ​

to a single row​

in a new worksheet.

But then your image seems to show to a single Column
You said this:

copy DataSheet A1:A40 to NewSheet ​

​

A1​

​

and add 1 blank column at end.
copy DataSheet B1:B40 to NewSheet ​

​

A42​

​

and add 1 blank column at end.
copy DataSheet C1:C40 to NewSheet ​

​

A:84​

​

and add 1 blamk column at end.

This seems to mean Copy all data to one column ​

My script copies all the data to row(1) on new sheet.

If this is not what you want please explain

​

 

[Rick Rothstein]

I have 40+ columns of data in 100s of rows.

At a specified time each day I need to copy each row sequentially to a single row in a new worksheet.

As an example:

DataSheet is the sheet from which data should be copied. NewSheet is the receiving sheet.

Process would be as follows;

copy DataSheet A1:A40 to NewSheet A1 and add 1 blank column at end.
copy DataSheet B1:B40 to NewSheet A42 and add 1 blank column at end.
copy DataSheet C1:C40 to NewSheet A:84 and add 1 blamk column at end.
copy DataSheet D1:D40 to NewSheet A:125 and add 1 blank column at end.
Continue to end of rows.

# of columns and rows may change from day to day, but will always be the same # of each on any particular sheet. NewSheet would be named according to date procedure is ran, ie; 1_23_18 for Jan 23 2018.

I am confused by your thread's title and the description of your question. Your title say you want to copy multiple rows to new single row sequentially, but your description shows you copying columns of data, not rows and your described placement appears to be in a single column, not row. And I do not understand why you are saying to add a blank column at the end when you are placing data vertically down Column A. Can you please clarify your question for us?

 

[My Aswer Is This]

My script copies each column of data starting in column(1) and continues till it finds no more data in Row(1)

And transposes all this data into a new sheet named Today's date
Pasting into Row(1) on new sheet and skips one cell in row(1) each time.

 

[jetpack]

Thank you for your time and help. Sorry if the explanation was fuzzy. I'll try to be clearer.

1. All the data should be copied from the Data sheet, which contains rows and columns, to a new sheet which will consist of one row.

2. The data would be copied in sequential row order with a blank cell at the end of each row that is added to visually separate the rows that were copied. For example a 40 column original row would take up 41 columns of the single row in the new sheet.

So if 1st row starting at A:1 is 40 columns, the sub would copy that 40 column row to the new sheet and add a blank column at the end so that the next iteration of copying would start at A:42, then the next row would be copied to A:84 and would take up 41 columns, etc. to the end of the copy process.

The desired result would be that if the sheet contains 40 columns and 10000 rows, the new sheet would contain 1 row of (40+1) x 10000 columns = 41000 columns.

I ran the code that you very generously supplied and the results are as follows;

The error message appears stating that the newly named sheet already exists, even though it does not show on the tab.

When I click on the OK button, the new tab then appears.

The code is copying everything in column A,(instead of row A) in sequential order to a single row in the newly created sheet. It terminates after reaching the last row in column A that contains data.

Can the task be scheduled to run automatically Sun thru Fri at a user defined time? Since the resulting sheet will be quite large and impossible to verify visually, is there some way to automatically validate that the copied data is the same as the original after the copy process? Can the data be copied to new sheet starting at row B on new sheet to leave room for comments on the new sheet?

Thanks again for your help and attention.

 

[jetpack]

Thank you for taking your time to assist me. Not a programmer so I am obviously describing things exactly opposite to what I need. And I thought it was all so clear. Just shows to go ya...

This is the reply I sent to My Answer is This;

Thank you for your time and help. Sorry if the explanation was fuzzy. I'll try to be clearer.

1. All the data should be copied from the Data sheet, which contains rows and columns, to a new sheet which will consist of one row.

2. The data would be copied in sequential row order with a blank cell at the end of each row that is added to visually separate the rows that were copied. For example a 40 column original row would take up 41 columns of the single row in the new sheet.

So if 1st row starting at A:1 is 40 columns, the sub would copy that 40 column row to the new sheet and add a blank column at the end so that the next iteration of copying would start at A:42, then the next row would be copied to A:84 and would take up 41 columns, etc. to the end of the copy process.

The desired result would be that if the sheet contains 40 columns and 10000 rows, the new sheet would contain 1 row of (40+1) x 10000 columns = 41000 columns.

I ran the code that you very generously supplied and the results are as follows;

The error message appears stating that the newly named sheet already exists, even though it does not show on the tab.

When I click on the OK button, the new tab then appears.

The code is copying everything in column A,(instead of row A) in sequential order to a single row in the newly created sheet. It terminates after reaching the last row in column A that contains data.

Can the task be scheduled to run automatically Sun thru Fri at a user defined time? Since the resulting sheet will be quite large and impossible to verify visually, is there some way to automatically validate that the copied data is the same as the original after the copy process? Can the data be copied to new sheet starting at row B on new sheet to leave room for comments on the new sheet?

Thanks again for your help and attention.

RR,
If that is still unclear , please let me know. I appreciate any input and/or help.

 

[My Aswer Is This]

Your still confusing me:

Like here you just said this:

The code is copying everything in ​

column A

,(instead of row A) in sequential order to a single row in the newly created sheet. It terminates after reaching the last row in column A that contains data.

instead of row A

Excel has no row A

Excel has columns like column A and column B''

Excel has rows like row 1 and row 2

As far as you saying your getting a error saying you already have a sheet name that.

Well the error could be caused by something else being wrong.

Are you trying this on a empty workbook with no other sheets except the one with your data.

Are you wanting all this data copied to Row(1) of the new sheet?
If not I'm still not clear especially when you say things like Row A
​

_{^{<strike>
</strike>}}​

 

[jetpack]

Does this help?

Original data
221	0.85	-0.96	6.47	9.09	6.27	9.09
767	0.95	-0.33	5.76	8.25	5.73	8.15
20	1	0	5.48	7.61	5.17	7.61
908	0.88	-0.68	4.87	8.07	4.6	7.5
New sheet copied data
221	0.85	-0.96	6.47	9.09	6.27	9.09		767	0.95	-0.33	5.76	8.25	5.73	8.15		20	1	0	5.48	7.61	5.17	7.61		908	0.88	-0.68	4.87	8.07	4.6	7.5

<colgroup><col span="7"><col><col span="7"><col><col span="7"><col><col span="7"></colgroup><tbody>
</tbody>

 

[jetpack]

Yes of course I meant to say row 1.

Yes, new workbook with nothing but sample data.

It's clearer now to me why it was unclear to you, sorry.

Thanks again for your help and patience.