dates

[SAM2017]

I'm trying to workout formula whereby when a date (eg 26/09/17) is input in A1 Tab1 the SAME date changes automatically in Tab 2 A1. Next day input 27/09/17 in A1,Tab1 date changes automatically to SAME date in Tab 2,B1 and so on. . .This is becoming frustrating.

I trust someone will be able to advise (hopefully short formula). Cheers.

Sam

 

[Faysal Farooqui]

- In Sheet1!A2 type 27/09/17
- In Sheet2!A1 type =Sheet1!$A$2 and copy right next as much as you want
- Now when Sheet2!A1 shows 27/09/17, copy and value paste it.

- In next day type a new date in Sheet1!A2
- It will automatically shown in Sheet2!B1
- Now copy and value paste it.

By this way you can do it.

 

[vcoolio]

Hello Sam,

A Worksheet_Change event is another option. A simple code as follows should work:-

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
    
        If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
        Sheet1.Range("A1").Copy Sheet2.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
        Sheet2.Columns.AutoFit
        
Application.ScreenUpdating = True
Sheet2.Select

End Sub

Manually enter your very first date in both Sheet1 A1 and Sheet2 A1. When you change the date in Sheet1 A1 and click away (or press enter or down arrow), the code will transfer the new date to the next cell in row1 Sheet2 horizontally.

To implement the code:-

- Right click on the Sheet1 tab.
- Select "View Code" from the menu that appears.
- Paste the above code into the big white field that then appears.

I hope that this helps.

Cheerio,
vcoolio.

 

[SAM2017]

Cheers Faysal Farooqi. It worked perfectly!

 

[SAM2017]

vcoolio. when code pasted do I need to close (red X) the page?

 

[vcoolio]

Hello Sam,

No. You only need to go back to the main sheet and start your data entries.

Cheerio,
vcoolio.