Excel Macro Help

eddiet

New Member
Joined
Jun 3, 2008
Messages
18
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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?

Thank you in advance
 
Upvote 0
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:
Upvote 0
The answer to both the questions is to initialize firstNonHeaderRow.

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
firstNonHeaderRow = 3
 
Last edited:
Upvote 0
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

APG.JPG


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.

Thank you again in advance
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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