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

twhitehead

New Member
Joined
Jun 28, 2012
Messages
8
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,096
Hello and welcome to the Board. See if this does it:

Code:
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

New Member
Joined
Jun 28, 2012
Messages
8
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

Well-known Member
Joined
Oct 30, 2011
Messages
4,096
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.

Code:
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

New Member
Joined
Jun 28, 2012
Messages
8

ADVERTISEMENT

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

New Member
Joined
Jun 28, 2012
Messages
8
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

Well-known Member
Joined
Oct 30, 2011
Messages
4,096

ADVERTISEMENT

Hi

This worked fine with the Brazilian version of Excel 2007:

Code:
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

New Member
Joined
Jun 28, 2012
Messages
8
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:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,981
Members
414,115
Latest member
SFUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top