Create Marco to copy data and to paste in worksheets

Hiten Vakharia

Board Regular
Joined
Sep 25, 2013
Messages
66
Hi,
I would like to create a macro where data is picked from the row and is copied to 10 additional worksheet on the same spreadsheet.
If the worksheet is not the spread sheet then to create a new worksheet and paste the data.

eg. On sheet1 I have;
Row 1 - abc
Row 2 - def
Row 3 - ghi
Row 4 - jkl
Row 5 - mno
Row 6 - pqr
Row 7 - stu and so on....

I would like to copy ENTIRE row1 and to be pasted on sheet2, same as row2 on sheet3 and so on....

Please let me know if I am not clear

Thanks in advance

Regards,
Hiten
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi Hiten,

I'm trying to understand...

You'd like row1 of sheet1 to be copied to sheet2, row2 to be copied to sheet3, etc...

Which row should the data be pasted to each time? Should it always be pasted in Row1 or should it stay in the same row it was in on sheet1?

How many total rows are there?


-Matt
 
Upvote 0
Hi Matt,

You'd like row1 of sheet1 to be copied to sheet2, row2 to be copied to sheet3, etc... -----YES

There are 10 rows

It is should be pasted on Row1

Hiten
 
Upvote 0
Hiten,

Try this:

Sub Paste_To_Sheets()
Dim row As Integer
Application.ScreenUpdating = False

sheets("Sheet1").Select

For row = 1 To 10
ActiveSheet.rows(row).EntireRow.Copy
sheets("Sheet" & row + 1).Select
ActiveSheet.Range("A1").PasteSpecial
Range("A1").Select
sheets("sheet1").Select
Next row

Application.CutCopyMode = False


End Sub

 
Upvote 0
Try this:
Code:
Sub Copy_Rows()
'Modified 11/16/2018 12:15:47 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Sheets(1).Activate
For i = 2 To 11
    Rows(i - 1).Copy Sheets(i).Rows(1)
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Well do you have at least 12 sheets.

My script assumes you have at least 12 sheets

You said:

and so on....

How do I know what so on means?
 
Upvote 0
Thanks for clarifying, now I have added 11 more sheets and it worked.

What I meant was if there was only one worksheet - Sheet1, it should create Sheet2, Sheet3, Sheet4......Sheet10 and then paste the data from Sheet1 to Row1-Sheet2, Row1-Sheet3, ......Row1-Sheet10

Is it possible for you to modify the script.

Apologies for the confusion.

Regards,
Hiten
 
Upvote 0
The question is how many sheets do I need?

Your wanting to create a sheet for each row of data

In your post you had

For
row = 1
To
10

How did you know 10 ?

Do I need to count how many cells in column A have data to determined how many sheets I need?

And So if there are already 14 sheets in the workbook and there are 40 rows in sheet one then I need to make 27 new sheets. Because your master sheet would not be included.

And for all the sheets already created the script would overwrite what ever data is already in row(1)

And how do I name all these new sheets? Just let the script give them default names.

And every time we run this script does it overwrite the data already in row (1) of those sheets already created.

See these are the details I need.

And next time you run the script if there are only 10 rows of data then we just overwrite the data in row 1 of sheets 2 to 11 is this correct?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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