I have a sheet ("Division") that is fed by a macro on an application
form. The user hits the macro button and the "Division" sheet is
populated. However, I need this to then feed 5 other sheets depending
on the geographical Region . Is there anyway of either getting the
macro to populate the main "Division" sheet and simultaneously one of
the geopgraphical regions, or Getting the Division sheet to feed the
regions sheet.

I'll post the code I've already got for populating the "division" if
that helps.

Thanks


--
M3Cobb
------------------------------------------------------------------------
M3Cobb's Profile: http://www.excelforum.com/member.php...o&userid=24986
View this thread: http://www.excelforum.com/showthread...hreadid=385172

This should be possible, but your post is a little vague to answer completely.

-How can you tell which of the 5 region sheets needs populating once the
division sheet is populated- is there a particular cell on the Division sheet
with this information?
- How are the individual region sheets identified- are the sheet tab names
e.g. North, South etc.?

The easiest solution may be to make a new copy of the entire Division sheet
once it has been populated, then rename it for the appropriate region-
something like this:
Thisworkbook.Sheets("Division").Copy
after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets .Count)
ActiveSheet.Name = "South Region"

It may also be useful to post the relevant bits of your existing code as well.

Cheers,
Dave

"M3Cobb" wrote:


I have a sheet ("Division") that is fed by a macro on an application
form. The user hits the macro button and the "Division" sheet is
populated. However, I need this to then feed 5 other sheets depending
on the geographical Region . Is there anyway of either getting the
macro to populate the main "Division" sheet and simultaneously one of
the geopgraphical regions, or Getting the Division sheet to feed the
regions sheet.

I'll post the code I've already got for populating the "division" if
that helps.

Thanks


--
M3Cobb
------------------------------------------------------------------------
M3Cobb's Profile: http://www.excelforum.com/member.php...o&userid=24986
View this thread: http://www.excelforum.com/showthread...hreadid=385172

This is a shortened version as it's rather repetitive. The key cells ar
B20:C20 as they contain the region name.


Code
-------------------
Sub macro1()
'
' macro1 Macro
' Macro recorded 21/04/2005 by Mike Cobb
'

'
If Sheet1.Range("C4").Value = vbNullString Then
MsgBox "******** Please fill in the PHC Number ********", vbCritical
Exit Sub
End If

If Sheet1.Range("H49").Value = vbNullString Then
MsgBox "******* The DOV Has Not Been Signed and Returned *******", vbCritical
Exit Sub
End If

Workbooks.Open FileName:= _
"\\wdb.local\burton\work\JML\REM\12345.xls"
Sheets("Division").Select
Range("A8").Select
Selection.EntireRow.Insert
Windows("Fastrac formmac.xls").Activate
Range("C4").Select
Selection.Copy
Windows("12345.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Fastrac formmac.xls").Activate
Range("F4:G4").Select
Application.CutCopyMode = False
Selection.Copy
Windows("12345.xls").Activate
Range("B8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C8").Select
Windows("Fastrac formmac.xls").Activate
Range("F6:G6").Select
Application.CutCopyMode = False
Selection.Copy
Windows("12345.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D8").Select
Windows("Fastrac formmac.xls").Activate
Range("E11:F11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("12345.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E8").Select
Windows("Fastrac formmac.xls").Activate
Range("E14:F14").Select
Application.CutCopyMode = False
Selection.Copy
Windows("12345.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F8").Select
Windows("Fastrac formmac.xls").Activate
Range("B14").Select
Application.CutCopyMode = False
Selection.Copy
Windows("12345.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G8").Select
Windows("Fastrac formmac.xls").Activate
Range("B20:C20").Select
Application.CutCopyMode = False
Selection.Copy
Windows("12345.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H8").Select
Windows("Fastrac formmac.xls").Activate
Range("E20:F20").Select
Application.CutCopyMode = False
Selection.Copy
Windows("12345.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("I8").Select

End Su
-------------------

--
M3Cob
-----------------------------------------------------------------------
M3Cobb's Profile: http://www.excelforum.com/member.php...fo&userid=2498
View this thread: http://www.excelforum.com/showthread.php?threadid=38517