Previous Topic Next Topic

Data from one master sheet to 100 other sheet

Closed
anakonda93 Posts 2 Registration date Thursday 3 May 2018 Status Member Last seen 4 May 2018 - 3 May 2018 à 07:47
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 7 May 2018 à 11:01
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!


1 response

Réponse 1 / 1
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
3 May 2018 à 10:51
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 3 May 2018 Status Member Last seen 4 May 2018
4 May 2018 à 02:00
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
4 May 2018 à 16:09
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 12 September 2010 Status Contributor Last seen 27 December 2022 555
7 May 2018 à 11:01
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

Similar discussions

Copying data from one Excel sheet to another.
Remedial -
Kav -

30 responses
Extract data from one sheet to another
phanikr -
Mazzaropi -

1 response
Creating 1 master sheet from data from multiple sheets
ExcelNoNothing -
vcoolio -

1 response