worksheets

[ParTeeGolfer]

Is there a way to automatically insert a new worksheet within a workbook if
data from another workbook equals a certin value. the name the tab from the
last tab number used.

Example:

IF a1 in workbook "a" = "x" thn insert new worksheet from c:/templates
(template Sheet) in workbook B and name it ("Game" #) where # = count tabs
+1

Thanks in advance

 

[Chip]

I am working on it, but is workbook b going to determined by the user
(ie.e from an open dialog) or is it in a fixed location on your comp?

 

[Chip]

Here is a start...

current = Application.ActiveWorkbook.Name

'Need to get workbookb to be called when Workbook b is active
workbookb = Application.ActiveWorkbook.Name


If Range("A1").Value = "X" Then

Workbooks.Open Filename:="C:\Template.Xls"
template = Application.ActiveWorkbook.Name
Sheets(1).Select
Application.ScreenUpdating = False
ActiveSheet.Copy Before:=Workbooks(workbookb).Sheets(1)


countofsheets = ActiveWorkbook.Sheets.Count + 1
ActiveSheet.Name = "Game #" & countofsheets
Else
Exit Sub
End If

 

[Chip]

Here is a start...

current = Application.ActiveWorkbook.Name

'Need to get workbookb to be called when Workbook b is active
workbookb = Application.ActiveWorkbook.Name


If Range("A1").Value = "X" Then

Workbooks.Open Filename:="C:\Template.Xls"
template = Application.ActiveWorkbook.Name
Sheets(1).Select
Application.ScreenUpdating = False
ActiveSheet.Copy Before:=Workbooks(workbookb).Sheets(1)


countofsheets = ActiveWorkbook.Sheets.Count + 1
ActiveSheet.Name = "Game #" & countofsheets
Else
Exit Sub
End If

 

[ParTeeGolfer]

I am working on it, but is workbook b going to determined by the user
(ie.e from an open dialog) or is it in a fixed location on your comp?

No Workbook B is acually called "Individual Stats"

Workbook A is called "RUSH"

Also I need to tell you that I need to search A1 for the laast "x" in that
column in rush as this will be changed frequently (Counting the X's )might
work for the Game #

Hope you understand what I am looking for, The first description is preyy
close however the numer of "x's" will aid in the (GAME "x")tab name

 

[ParTeeGolfer]

Chip,

I also need to determine if the column A1 in "rush.xls"has a new "X" in
it.

If a new "x " ins inserted into column A ......then thats when a new
sheet from my template will ber insertedto"individule stats.xls" with the
name, Game #

I think that this should clear up any confusion.

I do thank you for your help!

 

[Chip]

Here is a start...

current = Application.ActiveWorkbook.Name

'Need to get workbookb to be called when Workbook b is active
workbookb = Application.ActiveWorkbook.Name


If Range("A1").Value = "X" Then

Workbooks.Open Filename:="C:\Template.Xls"
template = Application.ActiveWorkbook.Name
Sheets(1).Select
Application.ScreenUpdating = False
ActiveSheet.Copy Before:=Workbooks(workbookb).Sheets(1)


countofsheets = ActiveWorkbook.Sheets.Count + 1
ActiveSheet.Name = "Game #" & countofsheets
Else
Exit Sub
End If

 

[Chip]

Ok, here is what I got:

Private Sub Worksheet_Change(ByVal Target As Range)

temp = Target.Address
tempcolumn = ActiveCell.Column
If tempcolumn = 1 Then
If Target.Value = "X" Then




Workbooks.Open Filename:="C:\Template.Xls"

template = Application.ActiveWorkbook.Name
Workbooks.Open Filename:="C:\Individual Stats.xls"
indivstats = Application.ActiveWorkbook.Name
Workbooks(template).Activate
Sheets(1).Select
ActiveSheet.Copy Before:=Workbooks(indivstats).Sheets(1)


countofsheets = ActiveWorkbook.Sheets.Count + 1
ActiveSheet.Name = "Game #" & countofsheets





Else
Exit Sub
End If
Else
End If
End Sub




You need to add this macro to the sheet, not as a module. Also, I
assumed that Individual Stats.xls was in the C: directory.

 

[ParTeeGolfer]

Chip,

Not sure what you mean when you say to run as a Macro not a module.
Can you elaborate?

Thanks

 

[Gord Dibben]

Par

This is worksheet event code.

Right-click on the sheet tab and "View Code".

Paste in there. Code runs when a change is made.

Regular macros would be pasted into a General Module.


Gord Dibben Excel MVP

 

[ParTeeGolfer]

Par

This is worksheet event code.

Right-click on the sheet tab and "View Code".

Paste in there. Code runs when a change is made.

Regular macros would be pasted into a General Module.


Gord Dibben Excel MVP




Thanks Gord,

will try it

 

[ParTeeGolfer]

For some reason the tab that is inseted always starts with 3 not 2 if there
is only one tab or worksheet to start

 

[Chip]

Are there any hidden sheets in the workbook?

 

[ParTeeGolfer]

Are there any hidden sheets in the workbook?

not that I am AWARE of.... I am the one who came up with the workbook and I didnt hide any sheets!

 

[Chip]

Try this, I havent looked at this code in a little while, but where I
said:

countofsheets = ActiveWorkbook.Sheets.Count + 1
Change it to:

countofsheets = ActiveWorkbook.Sheets.Count

 

[ParTeeGolfer]

Try this, I havent looked at this code in a little while, but where I
said:

countofsheets = ActiveWorkbook.Sheets.Count + 1
Change it to:

countofsheets = ActiveWorkbook.Sheets.Count

Ok Chip,

That worked now the issue I have is that it puts every New tab next to the
first tab in the workbook instead of the very last. Also it makes me close
out of the workbooks that contain the template and individule stats after
every on inserted or I will get a file open error. How can I solve these
issues?

 

[ParTeeGolfer]

Try this, I havent looked at this code in a little while, but where I
said:

countofsheets = ActiveWorkbook.Sheets.Count + 1
Change it to:

countofsheets = ActiveWorkbook.Sheets.Count

Chip,

this worked however, it always puts the new Game # worksheet next to the
game 1 worksheet. I want to keep the games in numeric order. Also I can only
inset one workshhet at a time then I have to close both the template workbook
and individule stats workbook or else I will get an alrteady open error.

Can you tell me how to correct these two issues?

Thanks