Date Format Change in VBA

[hywt1]

Hello all, I've created some VBA that does the following:

• creates new sheet in existing workbook
• opens file containing SAP derived order book
• copies data and pastes into previously created new sheet

When this happens the date format is changed for about 25% of lines from date to general. There's nothing in the code to ask for this.

Any ideas why?

 

[Joe4]

Welcome to the Board!

Can you post the VBA code? Especially the part that opens the data?

Note, since you already have VBA code, can't you just add a line or two of code that formats the column the way you want after import?
Or is the issue really more than Formatting, and a Data Type thing (it would be helpful if you could post a few values that are showing up properly, and a few that are not, so we can see what they look like)?

 

[SpillerBD]

It is probably in the SAP derived source where you have the General formatting coming from.

 

[hywt1]

Sorry, for some reason it seems I can't past into this forum. Even typing at normal speed is next to impossible.


Spiller - the excel file containing the data has everything in the correct date format. The change is noticeable after pasting in to the new sheet.

 

[Joe4]

Even typing at normal speed is next to impossible.

Are you posting from a phone or something? It shouldn't be an issue (I have never had an issue).
I would be interested to hear what kind of device and browser you are trying to post from.

If you wish to paste images to this forum, you would need to use some of the tools mentioned in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
There is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

Or you couold just type in a few examples of that the entries look like.

the excel file containing the data has everything in the correct date format. The change is noticeable after pasting in to the new sheet.

Then it may be how you are pasting it.

 

[hywt1]

Hello Joe

DestoprunnigWindow 7 / IE11

Ta was typed at normal speed an as meant to read Desktop running Windows 7

 

[hywt1]

Typing on phone works fine. I’ll see if I can paste the code when I go back to work.

 

[Joe4]

Hello Joe

DestoprunnigWindow 7 / IE11

Ta was typed at normal speed an as meant to read Desktop running Windows 7

Personally, I find that I get the best performance using Google Chrome, but IE should work fine too.
If you are having issues, I would probably start looking at computer configuration or internet connection/network issues.
Many compamies put virus protection/firewalls that can affect performance.
A good test would be to see if you experience similar issues with other similar forums on the internet.

 

[hywt1]

' GetTodaysFilename Macro
'

'
Dim TodaysFilename As String

Sheets("Workspace").Select
Range("B12").Select
Selection.ClearContents
Range("B10").Select
Selection.Copy
Range("B12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

TodaysFilename = Range("B12").Value

Workbooks.Open Filename:=TodaysFilename


' Copy New Order Book Data And Close File

'
Selection.CurrentRegion.Select
Selection.Copy
Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True


'

Windows("Open Order Data.xlsm").Activate
Sheets("CURRENT").Select
Range("A1").Select
ActiveSheet.Paste
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Insert Shift:=xlToRight

 

[Joe4]

Can you post a couple of examples of what these values look like (some of both kinds?).