Auto Copy Contents of 1 WS to Another Based on Value of 1 Cell

**durdensbuddy** · 02-21-2010, 06:57 AM

Hello Ladies and Gentlemen,

I come to you seeking assistance. I am currently working on designing a semi-interactive data base for work, but am coming across some stumbling blocks.

FYI- At work I have access to MS Excel 2k3, but if the solution is more easily achieved in 2k7 I have that at home.

Short story long: I am a security guard who does hourly patrols of my site and must log certain information, as well as any security violations I observe. Currently this is done by hand with no tracking or ability to observe "trends".

I have designed a rudimentary front pagewith all the categories I need, I even managed to get a dynamic date, as well as Weekday squares. Which leads to my question:

Is there a formula that would copy the contents of one worksheet to another?

What I would like is a formula along the lines of "IF A8 = MONDAY COPY WORKSHEET "CURRENT" TO wORKSHEET "MONDAY"

As I'm sure you can tell from the syntax above, I'm new to advanced functions in excel. Any ideas, suggestions, recommended readings?

Thanks

**durdensbuddy** · 02-21-2010, 07:04 AM

Here's an edited (and unformatted) version of what I am working with.

**JeanRage** · 02-21-2010, 07:09 AM

Hi,

Yes ... it is feasible in different ways
1. Fully automatic, say on the input of a given cell
2. With a macro called by a command button ...
3. or even with a series of formulas ...

**durdensbuddy** · 02-21-2010, 07:27 AM

Thank you for your reply jean, I will try not to monopolize your time, can you point me in the right direction in how to implement them. All of your suggestions just went whizzing right over my head.

**martindwilson** · 02-21-2010, 07:30 AM

following copies sheet 1 and gives it the name in cell a1 of sheet 1

Code:

Sub copy_sheet()
Application.ScreenUpdating = False
    On Error GoTo errorhandler
    Sheets("Sheet1").Copy After:=Sheets("Sheet1")
    Sheets("Sheet1 (2)").Name = Sheets("Sheet1").Range("a1").Value
    
    Application.ScreenUpdating = True
    Exit Sub
errorhandler:
 
  Application.DisplayAlerts = False
  Sheets("Sheet1 (2)").Delete
   Application.DisplayAlerts = true 
MsgBox "sheet name already exists or has illegal characters "
  Application.ScreenUpdating = True
End Sub

**durdensbuddy** · 02-26-2010, 08:54 AM

Thank you for sharing the code, now can I push my luck and ask how to edit it to target A8

Basically I need the first section of code for A8 = Monday copy to worksheet 2 "Monday"

Also would i have to add the code in seven times or can I continue editing with if a8 = tuesday, etc.

Thanks