# Excel Macro Help

#### eddiet

##### New Member
Hey,

I think I got my last issue resolved now, so I need some more additional help because this code will be beyond my self-taught skills.

This is what I'm trying to do.

I have a table of information that for example has Y number of columns of information. Lets then say that I have X amount of rows of information. What I'm trying to do is create a macro that will count the number of rows, because it'll be a variable amount of data in real life, and then create X amount of tabs and then populate this standard template (the standard template would be tab#1) that I have with the respective information from that table. So basically its like when you use Excel and Word to create individual letters.

So to sum it up, I want to have this macro to count the number of rows of information, then make that number of tabs of a standard template (the standard template would be tab#1), and then populate this template with the respective information.

Can anyone help me get started, I like a challenge so its not completly necessary to give me all the code, just a good way to start (like how to get the macro to "count" the rows of data and then make copies of the template).

Thanks

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### mikerickson

##### MrExcel MVP
Code:
``ActiveSheet.Cells(Rows.Count,1).End(xlup)``
returns the last filled cell in column 1.
And
Code:
``ActiveSheet.Cells(Rows.Count,1).End(xlup).Row``
returns the row number of that cell.

Overall, it sounds like you want to create some new sheets and copy one row to each of the new sheets. Is that the situation?

#### eddiet

##### New Member
Code:
``ActiveSheet.Cells(Rows.Count,1).End(xlup)``
returns the last filled cell in column 1.
And
Code:
``ActiveSheet.Cells(Rows.Count,1).End(xlup).Row``
returns the row number of that cell.

Overall, it sounds like you want to create some new sheets and copy one row to each of the new sheets. Is that the situation?

Basically yes in a nutshell. The thing is that, for example, lets say I have this table of data. I want A1 (from table) to go to B5 of the template, B1 (from table) to go to C9 of the template, etc...

I got the code written to make the copies of the "template" based on what I input, and although that would work, I guess still using some sort of "count" would make things easier.

#### eddiet

##### New Member
So far this is the code I have based on my input to make X amount of copies of the template worksheet.

Dim x As Integer

x = InputBox("How many copies of TOE do you need??")
For numtimes = 1 To x
'Loop by using x as the index number to make x number copies.
ActiveWorkbook.Sheets("Blank").Copy _
After:=ActiveWorkbook.Sheets("Blank")
Next

#### mikerickson

##### MrExcel MVP
I would use this logic, copying the data to each new sheet as soon as it is created.

Code:
``````Dim x As Integer, numtimes as Integer
Dim newSheet as Worksheet

x = InputBox("How many copies of TOE do you need??")

'Loop by using x as the index number to make x number copies.

For numtimes = 1 To x
Set newSheet = ActiveWorkbook.Sheets("Blank").Copy After:=ActiveWorkbook.Sheets("Blank")

Rem copy data from Sheets("Blank") to newSheet

Next numtimes``````

If the number of new sheets is dependent on the number of filled rows in Blank you could use something like this
Code:
``````x = ActiveWorkbook.Sheets("Blank").Cells(Rows.Count,1).End(xlup).Row

For numtimes = firstNonHeaderRow to x
Rem code
Next numtimes``````

Last edited:

#### eddiet

##### New Member
Sub Copy()
Dim x As Integer

x = ActiveWorkbook.Sheets("APG").Cells(Rows.Count, 1).End(xlUp).Row
For numtimes = firstNonHeaderRow To x
Rem code
'Loop by using x as the index number to make x number copies.
ActiveWorkbook.Sheets("Blank").Copy _
After:=ActiveWorkbook.Sheets("Blank")
Next numtimes
End Sub

Thats the code I'm using. APG is the worksheet with the data, I filled A1:A4 with data. Doing this it should give me 4 copies of the "Blank" worksheet, but instead its given me 5 copies, thoughts?

#### eddiet

##### New Member
Sub Copy()
Dim x As Integer
Dim y As Integer

x = ActiveWorkbook.Sheets("APG").Cells(Rows.Count, 1).End(xlUp).Row
y = x - 1
For numtimes = firstNonHeaderRow To y
Rem code
'Loop by using x as the index number to make x number copies.
ActiveWorkbook.Sheets("Blank").Copy _
After:=ActiveWorkbook.Sheets("Blank")
Next numtimes
End Sub

I did this and it works, but I must be missing something so that it could be cleaner.

Also, how would I start the count for example on the 3rd row (theres some header rows)??

Thanks again

Last edited:

#### mikerickson

##### MrExcel MVP

In Post #6, it was at its null value (0), and the 0 to 4 loop produced five sheets. The variable y should be removed, since this can be controlled by initializing firstNonHeaderRow to 1.

If you want to start on the third row, put this somewhere before the loop starts.
Code:
``````Dim firstNonHeaderRow as Integer

Last edited:

#### eddiet

##### New Member
thanks for the help, but it doesn't seem to be working. I'm thinking that perhaps it has to do with the fact that there are some merged cells.

Heres an image of the table

I've attached an image of what I'm referring to.

For now I have it working with this after the X = ....

x = (x - 10) / 4

It accomplishes what I need, but I guess I would like to understand more (trying to teach myself things) why the other method isn't working out that well.

Last edited:

#### mikerickson

##### MrExcel MVP
What does "isn't working" mean?
The posted codes are only creating the new sheets. The "copy data" part of the code needs to be written to replace the comment inside the loop.

Replies
5
Views
299
Replies
5
Views
295
Replies
6
Views
308
Replies
10
Views
481
Replies
0
Views
112

1,191,196
Messages
5,985,229
Members
439,950
Latest member
Xearo96

### 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.

### Which adblocker are you using?

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

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