I am trying to create a macro that will do the following

Create a new colum
Label the new column with today's day (Mon) in cell C1
Label the same column with today's date (05/17/04) in cell C2
Copy and paste what is in column A3:A50 & B3:B50 into C3:C50 & D3:D50
Copy and paste values what is cells A3:A50 & B3:B50 into those same cells

The problem I am running into is

Each time I run this macro I don't want it to overwrite the old data. For the next time I ran the macro I would want the new information posted to cells E3:E50 & F3:F50

I appreciate the help
Thanks.

Range("C:D").EntireColumn.Insert
Range("C1").Value = format(date,"ddd")
Range("C2").Value = Date
Range("C3:D50").Value = Range("A3:B50").Value

makes sense given what you describe.

--
Regards,
Tom Ogilvy


"wiinc1" wrote in message
...
I am trying to create a macro that will do the following:

Create a new column
Label the new column with today's day (Mon) in cell C1.
Label the same column with today's date (05/17/04) in cell C2.
Copy and paste what is in column A3:A50 & B3:B50 into C3:C50 & D3:D50.
Copy and paste values what is cells A3:A50 & B3:B50 into those same cells.

The problem I am running into is:

Each time I run this macro I don't want it to overwrite the old data. For
the next time I ran the macro I would want the new information posted to
cells E3:E50 & F3:F50.

I appreciate the help.
Thanks.

"Create a new column"
Sorry I missed this Part! But why do you need to "Create a new column" if your ranges are static
"today's day (Mon) in cell C1.
"today's date (05/17/04) in cell C2.
"Copy and paste what is in column A3:A50 & B3:B50
Unless you mean a new date stamp with each Column after B:, (try to be more specific next time)
then use this

Sub Dt_And_Copy(
Clm =
Do While Cells(3, Clm).Value < "
Clm = Clm +
Loo
Cells(1, Clm).Value = Format(Day(Now), "ddd"
Cells(2, Clm).Value = Format(Now, "mm/dd/yy"
Range("A3:B50").Copy Destination:=Cells(3, Clm
End Su


----- wiinc1 wrote: ----

I am trying to create a macro that will do the following

Create a new colum
Label the new column with today's day (Mon) in cell C1
Label the same column with today's date (05/17/04) in cell C2
Copy and paste what is in column A3:A50 & B3:B50 into C3:C50 & D3:D50
Copy and paste values what is cells A3:A50 & B3:B50 into those same cells

The problem I am running into is

Each time I run this macro I don't want it to overwrite the old data. For the next time I ran the macro I would want the new information posted to cells E3:E50 & F3:F50

I appreciate the help
Thanks.

All I meant by new column was that it would paste to the next open column. Sorry for any confusion

Since I have to do this operation on four seperate tabs within a workbook, how does this change the code

Thanks for your help.

It doesn't work because you are always referring to/working with the
activesheet in your code. Chris's code had the same mistake, so you have
reproduced it faithfully. A quick, inefficient fix would be to activate
each sheet in your loop.

For Each s In Sheets
s.Activate
If s.Name = "Q4FY04Contracts" Then

--
Regards,
Tom Ogilvy

"wiinc1" wrote in message
...
I tried your suggestion and the result was it copied and pasted the two
columns of the active sheet next to the columns. It did not do it to each
individual sheet. So, taking what you created I tried:

Sub Dt_And_Copy()
For Each s In Sheets
If s.Name = "Q4FY04Contracts" Then
Clm = 3
Do While Cells(3, Clm).Value < ""
Clm = Clm + 1
Loop
Cells(1, Clm).Value = Format(Day(Now), "ddd")
Cells(2, Clm).Value = Format(Now, "mm/dd/yy")
Range("A3:B50").Copy Destination:=Cells(3, Clm)
End If
If s.Name = "Q5FY05Contracts" Then
Clm = 3
Do While Cells(3, Clm).Value < ""
Clm = Clm + 1
Loop
Cells(1, Clm).Value = Format(Day(Now), "ddd")
Cells(2, Clm).Value = Format(Now, "mm/dd/yy")
Range("A3:B50").Copy Destination:=Cells(3, Clm)
End If
If s.Name = "OpenItems 2004" Then
Clm = 3
Do While Cells(3, Clm).Value < ""
Clm = Clm + 1
Loop
Cells(1, Clm).Value = Format(Day(Now), "ddd")
Cells(2, Clm).Value = Format(Now, "mm/dd/yy")
Range("A3:B50").Copy Destination:=Cells(3, Clm)
End If
If s.Name = "OpenItems 2005" Then
Clm = 3
Do While Cells(3, Clm).Value < ""
Clm = Clm + 1
Loop
Cells(1, Clm).Value = Format(Day(Now), "ddd")
Cells(2, Clm).Value = Format(Now, "mm/dd/yy")
Range("A3:B50").Copy Destination:=Cells(3, Clm)
End If
Next
End Sub


But this doesn't seem to work either. It works for the sheet labelled
open items 2005, but none of the other sheets. Can you shed some light on
were I went wrong?

Thanks again