Adding current date to a header record with a macro - Need help today

[Cynthia Zone]

Hello,

Is there a code to insert todays date in a line of text.

e.g.

Sub InsertHeader()
'
' InsertHeader Macro
' Insert Header to A1
'

'
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=+Sheet1!RC"
    Range("A2").Select
End Sub

Here is the header ( I need to keep the formatting but change the date to today's date every time the file is run. Currently the date reads 20140930.

Thank you so much. If someone could help me with this today I would appreciate it.

H10724196825306601ABC Company Limited20140930 SMB00001 1N

 

[Hui]

Try:

Sub InsertHeader()
'
' InsertHeader Macro
' Insert Header to A1
'
  Dim myDate As String
  Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
  myDate = Application.WorksheetFunction.Text(Date, "YYYYMMDD")
  Range("A1").FormulaR1C1 = "H10724196825306601ABC Company Limited" + myDate + " SMB00001 1N "
  Range("A2").Select
End Sub

 

[cynzone]

Hi Hui,

Thank you so much. The code worked except I have another file that has different numbers in the header. That is why I put the numbers on Sheet 1. All my macros are in the personal file not in the excel file so whichever spreadsheet I open they run.

Can I add the date (keeping the exact spacing - I had to insert spaces in your code) and keep the H.....up to the date in Sheet 1 or how can I tell it to enter H1 or H2. There is no differences in the file to point to.

regards,
Cynthia

 

[cynzone]

Hi Hui,

Any chance you can look at this again. Your code works perfectly if I did not have two different numbers to go in the header line. To do this I exported data into two different worksheets and hard coded the header.

Help,
THANK YOU
Cynthia

 

[Hui]

You can build the header string how ever you want

Can you supply a file with the apropriate text and header string layout

 

[cynzone]

Hi.

This is the header string
a) H10724196825306601ABC Company Limited+ todays date is one file
H10725196827321362ABC Company Limited+ todays date is one file.

I save this file in a text format and the positioning has to be exact. I added spaces to the code you sent me and it worked but it will always return with the first H10724....

I thought if I could hard code everything in the excel file and just insert the date??

My original code is in the first email. (I took text from one sheet and moved it to another) but could not change the date to today. I imported data into two spreadsheets one with H1 and the other H2.

With your code the text is no longer needed but..

I hope this helps

Thank you.


H10724196825306601ABC Company Limited20140930 SMB00001 1N

 

[cynzone]

Hi,
I hope this helps.
thank you
Cynthia