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]

edit

In your original post you said:

to NewSheet A1
To Newsheet A42

A1 is row 1
A42 is row 42​

​

 

[My Aswer Is This]

In post 6 you added a whole lot more to your request.

Like this:

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.


Well to have this automatically run on certain days at a certain time would then depend on a lot of things.

Is your computer turned on at this time?
Is Excel running at this time
And on and on.

If the computer is turned on at this time but excel is not running you would have to set this up some place in your computer settings telling the computer to launch Excel at this time. Then you would have to add this script to a On Open event when Excel is launched and then what happens if this or that.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.
_{^{<strike>
</strike>}}​

 

[jetpack]

Thanks for your reply.

Did the sample original and copied data that I sent help to clarify what I am trying to accomplish?

 

[My Aswer Is This]

Yes I understand what you want but do not know how to do this.

Each Column has a different number of cell and each row has a different number of cells with data

With data to be copied to a new sheet and put all this data into Row(2)

 

[My Aswer Is This]

Try this:

It copies all the data to Row(1) on new sheet.
Now in one post you said Row(1) then in other post you said Row(2)

But actually you kept saying Row(A) and Row(B)

Sub Copy_My_Data()
'Modified  12/1/2018  3:58:05 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
Dim LastColr 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
    
    LastColr = Sheets(1).Cells(i, Columns.Count).End(xlToLeft).Column
    Lastcc = Sheets(ans).Cells(1, Columns.Count).End(xlToLeft).Column + 2
        If i = 1 Then Lastcc = 1
    Sheets(1).Cells(i, 1).Resize(, LastColr).Copy
    Sheets(ans).Cells(1, Lastcc).PasteSpecial xlPasteValues
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "The sheet named  " & Date & " Already exist. I have stopped the script"

End Sub

 

[jetpack]

Great news!

The new sheet is created with no error message.

The code copies row by row and paste in single row with a blank column at the end of each copied row. Yea!

But it stops copying at the end of row 35. With the added blank column each row presently consist of 36 columns. So 35 rows x 36 columns = 1260 columns in total and excel can accommodate 16384 columns per row.

All help much appreciated.

Thanks again for your time and generosity.

 

[My Aswer Is This]

The script looks at row(1) of sheet(1) to see how many columns have data in Row(1)

So if you have data in A1 to H1

It would look at columns A to H

If that is not how I should look for data you tell me how do I know how many columns to check

I'm sure you do not have data in all 16,000 columns

 

[My Aswer Is This]

You said:

But it stops copying at the end of row 35.

When referring to information on a sheet you need to always refer to both Row and column

So you said end of row 35

Row 35 of what column?

You should say something like this ends at Range("H35")


​

 

[jetpack]

Like this?

Data is in columns A:AI. Is copying from row A1:AI35.

But there is data in Rows A1:A4000+.

 

[My Aswer Is This]

You original post said:

# of columns and rows may change from day to day, but will always be the same # of each on any particular sheet

But now your saying it will always be Column A to AI

Well then you must not always have data in Row one of column AI

Or my script would have worked

So are you saying it will always be column A to AI

But the number of rows in each column will change.

And if it will always be column A To AI where did you say that in your original post?


​