Data from one master sheet to 100 other sheet

Closed
anakonda93 Posts 2 Registration date Thursday May 3, 2018 Status Member Last seen May 4, 2018 - May 3, 2018 at 07:47 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 7, 2018 at 11:01 AM
Hello,

I have this kind of problem that i have serial numbers xxx-xx-xx listed in 1200 rows in A column of master sheet. I have already macro for creating 100 sheets and renaiming them from 1 to 100.

Now i need to automatically copy first 12 rows from master sheet column A and paste it to sheet 1, after that next 12 rows from master sheet column A to sheet 2 and so on. Basically to sheet 2 are copied data from column A rows 13-24 in master sheet and for sheet 3 is copied data from A column 25-36 rows from master sheet.

Thank you for you replies beforehand!


Related:

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
May 3, 2018 at 10:51 AM
Hi Anakonda,

Give the following code a try:
Sub RunMe()
Dim xRow, xSheet As Integer

Sheets("Master").Select

xRow = 1
xSheet = 1

Do
    Range(Cells(xRow, "A"), Cells(xRow + 11, "A")).Copy Sheets("Sheet " & xSheet).Range("A1")
    
    xRow = xRow + 12
    xSheet = xSheet + 1
Loop Until Range("A" & xRow) = vbNullString Or xSheet = 101
End Sub


Will this work for you?

Best regards,
Trowa
anakonda93 Posts 2 Registration date Thursday May 3, 2018 Status Member Last seen May 4, 2018
Updated on May 4, 2018 at 02:00 AM
Hi TrowaD,

Thank you for quick answer, I tried your code but it only copied first 12 rows from A column of master sheet to all other sheets.

Best regards,
Anakonda
Blocked Profile
May 4, 2018 at 04:09 PM
That is done by design. If you know what variable to change , change it to fit your model! You will learn that way. If he were to post the actual solution, what would you learn?
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
May 7, 2018 at 11:01 AM
Really?

Variable xRow = 1 and xSheet = 1

Then from column A, rows 1 (=xRow) to 12 (=xRow+11) are copied to sheet 1 (=xSheet).

Adjust variable xRow = 1 + 12 = 13 and xSheet =1 +1 =2

Next loop

Then from column A, rows 13 (=xRow) to 24 (=xRow+11) are copied to sheet 2 (=xSheet).

...

Do you maybe mean you don't want the 12 copied cells placed in A1:A12 of the numbered sheets?

Best regards,
Trowa