macro to copy date to sheet 2 as long as there is data in adjacent cell

[twhitehead]

I am trying to create a macro that will copy the date entered in sheet 1 cell A10 to sheet 2 B1 and copy down Column B: as long as there is data in sheet 2 column A.


i.e date 16/06/2012 in cell A10 in sheet 1
Sheet 2
A1 VH-123
A2 VH-XYZ
A3 VH-QWY
etc

I would like to copy 16/06/2012 to b1 and keep copying it down as long as the cell in adjacent in column A is not blank

So it will look like this, but if there is data in A1..A10 it will copy date from B1..B10

Sheet 2
A1 VH-123 B1 16/06/2012
A2 VH-XYZ B2 16/06/2012
A3 VH-QWY B3 16/06/2012

 

[Worf]

Hello and welcome to the Board. See if this does it:

Option Explicit


Sub WhiteHead()


    Dim mydate As Date, i%, targ As Worksheet
    Set targ = Worksheets("Sheet2")
    
    mydate = Worksheets("Sheet1").Range("a10").Value
    i = 1
    Do
        targ.Cells(i, 2).Value = mydate
        i = i + 1
    Loop Until targ.Cells(i, 1).Value = ""


End Sub

 

[twhitehead]

Thank you for your time it did exactly what I asked for but I failed to mention that I needed it to do the following

Once I have run the macro to copy the date, if I run the macro again I need it to copy the date to Sheet 2 column B but to paste it in the next blank cell in Column B, not overwrite what is alread there.

i.e. run macro first time it will appear as below

Column A: Column B
1 VH-123 16/06/2012
2 VH-XYZ 16/06/2012
3 VH-QWY 16/06/2012

If I change date in Sheet 1 A10 to 19/06/2012 and run the macro I want it to copy the date to the next blank cell in column b, (in this instance B4) not overwrite from cell B1.

Column A: Column B
1 VH-123 16/06/2012
2 VH-XYZ 16/06/2012
3 VH-QWY 16/06/2012
4 VH-QXY 19/06/2012
5 VH-ABW 19/06/2012


Thank you for your time, appreciate the assistance and apologise for not fully explaining requirements the first time

 

[Worf]

Hi
Please test this new version. Note that it will always place the date at the bottom of column B, even if this date already is on the list.
If you need further refinements just ask.

Option Explicit
Sub WhiteHead()
    
    Dim mydate As Date, i%, targ As Worksheet
    ' will always insert data at end of column B's used range
    
    Set targ = Worksheets("Sheet2")
    mydate = Worksheets("Sheet1").Range("a10").Value
    i = targ.Range("b" & Rows.Count).End(xlUp).Row + 1
    
    ' test if column B is empty
    If i = 2 And targ.Cells(1, 2).Value = "" Then i = 1
    
    While targ.Cells(i, 1).Value <> ""
        targ.Cells(i, 2).Value = mydate
        i = i + 1
    Wend
    
End Sub

 

[twhitehead]

Thank you this worked perfectly. But now I have been asked to make a change to the date so that it includes date + time ie 03/09/2012 12:08. The data is in the same cell of A10. Can you please let me know how I can change the macro so the date and time appear in the copied to cell. Thank you.

 

[twhitehead]

Thank you this worked perfectly. But now I have been asked to make a change to the date so that it includes date + time ie 03/09/2012 12:08. The data is in the same cell of A10. Can you please let me know how I can change the macro so the date and time appear in the copied to cell. Thank you.

 

[Worf]

Hi

This worked fine with the Brazilian version of Excel 2007:

Sub WhiteHead()
    
    Dim mydate As Date, i%, targ As Worksheet
    ' will always insert data at end of column B's used range
    
    Set targ = Worksheets("Sheet2")
    mydate = Worksheets("Sheet1").Range("a10").Value
    i = targ.Range("b" & Rows.Count).End(xlUp).Row + 1
    
    ' test if column B is empty
    If i = 2 And targ.Cells(1, 2).Value = "" Then i = 1
    
    While targ.Cells(i, 1).Value <> ""
        targ.Cells(i, 2).Value = mydate
        i = i + 1
    Wend
    targ.Range("b1:b" & i).NumberFormat = "[$-409]d/m/yyyy h:mm AM/PM;@"
        
End Sub

 

[twhitehead]

Thank you very much it work perfectly appreciate all the assistance you have given me

Hi

This worked fine with the Brazilian version of Excel 2007:

Sub WhiteHead()
    
    Dim mydate As Date, i%, targ As Worksheet
    ' will always insert data at end of column B's used range
    
    Set targ = Worksheets("Sheet2")
    mydate = Worksheets("Sheet1").Range("a10").Value
    i = targ.Range("b" & Rows.Count).End(xlUp).Row + 1
    
    ' test if column B is empty
    If i = 2 And targ.Cells(1, 2).Value = "" Then i = 1
    
    While targ.Cells(i, 1).Value <> ""
        targ.Cells(i, 2).Value = mydate
        i = i + 1
    Wend
    targ.Range("b1:b" & i).NumberFormat = "[$-409]d/m/yyyy h:mm AM/PM;@"
        
End Sub

 

[Worf]

You are welcome