macro - change format in other sheet

en_passant

New Member
Joined
Feb 10, 2016
Messages
13
Hi guys,



I'm trying to change the format of cells and move them to another excel sheet.


The format is this: 19/07/2016 03:22:49 - which is - yy(end of 2019)/mm/yy(beginning of 2019)dd, so yy/mm/yydd.


I'm changing it so it shows as dd/mm/yy and when I run this code - the format changes as I want it to (with the sheet already open):


' Workbooks.Open Filename:="C:\Users\epass\Desktop\800xxx.csv"
Windows("800xxx.csv").Activate
Range("B2:B10").Select
Selection.NumberFormat = "yy/mm/dd hh:mm:ss"


This macro is saved in another sheet. When I then close the sheet and run this:


Workbooks.Open Filename:="C:\Users\epass\Desktop\800xxx.csv"
Windows("800xxx.csv").Activate
Range("B2:B10").Select
Selection.NumberFormat = "yy/mm/dd hh:mm:ss"


It does not change the format. It remains as it is above. The format shows as having been changed but the text in the cell hasn't changed and whatever I do with the format options it won't allow it to change after that code has run.

Regards,
*e_p
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Watch MrExcel Video

Forum statistics

Threads
1,102,782
Messages
5,488,850
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top