Previous Topic Next Topic

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

Answer 1 / 1
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
0
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
0
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?
0
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
0