Can a template be created and stored in a vb module within another workbook
or does the vb code of the other workbook have to point to it to use it? I
have a lot of data that I am trying to copy to a formatted sheet with merged
cells, however when the search for data code fires it ends up with more rows
then can be stored on one formatted sheet, so I would like to be able to
create some type of code to count how many rows of data the search finds and
then copy the template to the working workbook, rename the new sheet. So if
the template has only say 13 rows of usable space for data storage and the
search finds 23 rows of data, I would like the code to create a new sheet
from the template and then create how many ever pages it needs to store the
data based off the first page of the template.(Only the first 17 rows of the
template are formatted, so they could be programmaticlly copied and pasted
into the same sheet).

Templetes are normally store with a XLA extension. Iff you post your code I
can make suggestions on how to accomplish what you want it is pretty simple .

"Mekinnik" wrote:

Can a template be created and stored in a vb module within another workbook
or does the vb code of the other workbook have to point to it to use it? I
have a lot of data that I am trying to copy to a formatted sheet with merged
cells, however when the search for data code fires it ends up with more rows
then can be stored on one formatted sheet, so I would like to be able to
create some type of code to count how many rows of data the search finds and
then copy the template to the working workbook, rename the new sheet. So if
the template has only say 13 rows of usable space for data storage and the
search finds 23 rows of data, I would like the code to create a new sheet
from the template and then create how many ever pages it needs to store the
data based off the first page of the template.(Only the first 17 rows of the
template are formatted, so they could be programmaticlly copied and pasted
into the same sheet).

Joel, I can post my code however it is fragmented and I have not been able to
get the two to work together yet.

"Joel" wrote:

Templetes are normally store with a XLA extension. Iff you post your code I
can make suggestions on how to accomplish what you want it is pretty simple .

"Mekinnik" wrote:

Can a template be created and stored in a vb module within another workbook
or does the vb code of the other workbook have to point to it to use it? I
have a lot of data that I am trying to copy to a formatted sheet with merged
cells, however when the search for data code fires it ends up with more rows
then can be stored on one formatted sheet, so I would like to be able to
create some type of code to count how many rows of data the search finds and
then copy the template to the working workbook, rename the new sheet. So if
the template has only say 13 rows of usable space for data storage and the
search finds 23 rows of data, I would like the code to create a new sheet
from the template and then create how many ever pages it needs to store the
data based off the first page of the template.(Only the first 17 rows of the
template are formatted, so they could be programmaticlly copied and pasted
into the same sheet).

I don't need working code, just a better idea of what you are doing. I
should be able to get the two to work together, or a least get you to the
next step.

"Mekinnik" wrote:

Joel, I can post my code however it is fragmented and I have not been able to
get the two to work together yet.

"Joel" wrote:

Templetes are normally store with a XLA extension. Iff you post your code I
can make suggestions on how to accomplish what you want it is pretty simple .

"Mekinnik" wrote:

Can a template be created and stored in a vb module within another workbook
or does the vb code of the other workbook have to point to it to use it? I
have a lot of data that I am trying to copy to a formatted sheet with merged
cells, however when the search for data code fires it ends up with more rows
then can be stored on one formatted sheet, so I would like to be able to
create some type of code to count how many rows of data the search finds and
then copy the template to the working workbook, rename the new sheet. So if
the template has only say 13 rows of usable space for data storage and the
search finds 23 rows of data, I would like the code to create a new sheet
from the template and then create how many ever pages it needs to store the
data based off the first page of the template.(Only the first 17 rows of the
template are formatted, so they could be programmaticlly copied and pasted
into the same sheet).

Joel,
Sorry for not getting back to you, got to busy at work and was not able to
reply back. Well here is what I have for code and I will try to explain what
I am trying to accomplish as best as possible.

Click event code:
Private Sub BtnGo_Click()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim T As String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

T = Me.CbxDept.Text
Set WS = Sheets("ProCode")
Set rng = WS.Range("A2:M" & Rows.Count)
WS.AutoFilterMode = False
On Error Resume Next
Application.DisplayAlerts = False
Sheets(T).Delete
Application.DisplayAlerts = True
On Error GoTo 0
rng.AutoFilter field:=13, Criteria1:="=" & T
Sheets("MASTER").Copy befo=Sheets(2)
Set WSNew = ActiveSheet
WSNew.name = T
WSNew.Cells(2, 10) = T

With Application
..ScreenUpdating = True
..EnableEvents = True
End With

End Sub

and here is the code I am try to use to programmacticlly copy that data to
the newly created sheet.

iterations = ws1.Range("A1").CurrentRegion.Rows.Count
For i = 1 To iterations
ws2.Cells(i = 1, 1) = ws1.Cells(i, 1)

so on and so forth to copy all the data that the search finds. If you would
like I could send you my file so you can see what it is that I am trying to
do. So far I have gotten it to create and rename the new sheet I just cannot
figure out how to get it to choose all the data that matches the users
selection. I figure it would be eaisest to take this one step at a time. This
is the first step:
1) grap what the user selects from combobox 'CbxDept'
2) find all the data that matches the selection from the left 2 characters
of column 'M' sheet 'ProCode'
3) then programmacticlly copy all that data to the newly created sheet.
Seems simple enough however the problem comes when it copies to the new
sheet. The master sheet can only hold 13 rows of data per sheet, but I am
trying to figure out how to get the code to determine how many rows of data
there is and then create multible pages from the master copy within the newly
created sheet(so if it finds 24 rows of data it should create a new sheet,
rename it to match the selection, then add an additional page to copy the
other 9 rows of data to). I hope I have not confused you, when I think about
it, it sounds so simple. I don't want to drag the reply out so I will stop at
step one.

Thanks for all your help Joel

Mekinnik


"Joel" wrote:

I don't need working code, just a better idea of what you are doing. I
should be able to get the two to work together, or a least get you to the
next step.

"Mekinnik" wrote:

Joel, I can post my code however it is fragmented and I have not been able to
get the two to work together yet.

"Joel" wrote:

Templetes are normally store with a XLA extension. Iff you post your code I
can make suggestions on how to accomplish what you want it is pretty simple .

"Mekinnik" wrote:

Can a template be created and stored in a vb module within another workbook
or does the vb code of the other workbook have to point to it to use it? I
have a lot of data that I am trying to copy to a formatted sheet with merged
cells, however when the search for data code fires it ends up with more rows
then can be stored on one formatted sheet, so I would like to be able to
create some type of code to count how many rows of data the search finds and
then copy the template to the working workbook, rename the new sheet. So if
the template has only say 13 rows of usable space for data storage and the
search finds 23 rows of data, I would like the code to create a new sheet
from the template and then create how many ever pages it needs to store the
data based off the first page of the template.(Only the first 17 rows of the
template are formatted, so they could be programmaticlly copied and pasted
into the same sheet).

I bet you meant .xlt extensions for Templates.

Joel wrote:

Templetes are normally store with a XLA extension. Iff you post your code I
can make suggestions on how to accomplish what you want it is pretty simple .

"Mekinnik" wrote:

Can a template be created and stored in a vb module within another workbook
or does the vb code of the other workbook have to point to it to use it? I
have a lot of data that I am trying to copy to a formatted sheet with merged
cells, however when the search for data code fires it ends up with more rows
then can be stored on one formatted sheet, so I would like to be able to
create some type of code to count how many rows of data the search finds and
then copy the template to the working workbook, rename the new sheet. So if
the template has only say 13 rows of usable space for data storage and the
search finds 23 rows of data, I would like the code to create a new sheet
from the template and then create how many ever pages it needs to store the
data based off the first page of the template.(Only the first 17 rows of the
template are formatted, so they could be programmaticlly copied and pasted
into the same sheet).

--

Dave Peterson