copy multiple rows to new single row sequentially in new worksheet

J

jetpack

Board Regular
Joined
Nov 4, 2010
Messages
81
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!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Try this:
Code: 
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
 
Upvote 0
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

​
 
Upvote 0
jetpack said:
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.
Click to expand...
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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>​
 
Upvote 0
Does this help?

[TABLE="width: 1832"]
<colgroup><col span="7"><col><col span="7"><col><col span="7"><col><col span="7"></colgroup><tbody>[TR]
[TD="colspan: 7"]Original data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]221[/TD]
[TD="align: right"]0.85[/TD]
[TD="align: right"]-0.96[/TD]
[TD="align: right"]6.47[/TD]
[TD="align: right"]9.09[/TD]
[TD="align: right"]6.27[/TD]
[TD="align: right"]9.09[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]767[/TD]
[TD="align: right"]0.95[/TD]
[TD="align: right"]-0.33[/TD]
[TD="align: right"]5.76[/TD]
[TD="align: right"]8.25[/TD]
[TD="align: right"]5.73[/TD]
[TD="align: right"]8.15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5.48[/TD]
[TD="align: right"]7.61[/TD]
[TD="align: right"]5.17[/TD]
[TD="align: right"]7.61[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]908[/TD]
[TD="align: right"]0.88[/TD]
[TD="align: right"]-0.68[/TD]
[TD="align: right"]4.87[/TD]
[TD="align: right"]8.07[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]7.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]New sheet copied data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]221[/TD]
[TD="align: right"]0.85[/TD]
[TD="align: right"]-0.96[/TD]
[TD="align: right"]6.47[/TD]
[TD="align: right"]9.09[/TD]
[TD="align: right"]6.27[/TD]
[TD="align: right"]9.09[/TD]
[TD][/TD]
[TD="align: right"]767[/TD]
[TD="align: right"]0.95[/TD]
[TD="align: right"]-0.33[/TD]
[TD="align: right"]5.76[/TD]
[TD="align: right"]8.25[/TD]
[TD="align: right"]5.73[/TD]
[TD="align: right"]8.15[/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5.48[/TD]
[TD="align: right"]7.61[/TD]
[TD="align: right"]5.17[/TD]
[TD="align: right"]7.61[/TD]
[TD][/TD]
[TD="align: right"]908[/TD]
[TD="align: right"]0.88[/TD]
[TD="align: right"]-0.68[/TD]
[TD="align: right"]4.87[/TD]
[TD="align: right"]8.07[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]7.5[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Pivot graph data when creating a new tab from a template.
Replies
0
Views
258
Nippyzap
N
N
  • Question Question
Copy Unique Values to New Sheet VBA
Replies
3
Views
692
nirvehex
N
Agnarr
  • Question Question
VBA problem with creating new sheets
Replies
0
Views
832
Agnarr
Agnarr
G
  • Question Question
VBA - add formula to cell when created
Replies
2
Views
516
Golaidron
G
C
  • Question Question
Create a new row for every date in row and create multiple copies of data to new worksheet based on cell number value for each date in the row VBA
Replies
0
Views
29
Chris258
C

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top