VBA and Excel Formatting

[mgoebel97]

Hi,
I need some help. I am copying data from one excel workbook to another. I only want a part of what is on a field but it doesnt seem to be working. I was hoping you can help me.

Please see below:
Range("D" & Rows.Count).End(xlUp).Offset(1).Select
Set x = Workbooks.Open("C:\Santa US Final.xls")
x.Sheets("NivelSer Critical Information").Range(Right("D12", 8)).Copy
Windows("Load Times - Cumbre v1 4.xlsm").Activate
ActiveCell.PasteSpecial
Selection.NumberFormat = "hh:mm:ss"

The field D12 has date and time in it but I only want the date. I am getting the date as well even though I have only the right side of the field. Any help would be greatly appreciated.

 

[Debraj]

.Range(Right("D12", 8)).Copy

Hi mgoebel97

Welcome to the forum..
Please upload your sample file.. You can not pass a date to a range section, It its a named range we have to trick a bit, sample file greatly appreciated.

 

[NARAYANK991]

Hi ,

I am not sure what you are trying to do ; the following statement :

x.Sheets("NivelSer Critical Information").Range(Right("D12", 8)).Copy

cannot work , in my opinion.

The Range object expects a string as a a parameter ; the RIGHT function also expects a string parameter.

The part RIGHT("D12",8) is not going to do what you expect it to do , since what you are passing to it is the string D12 , not the contents of the cell D12.

If you wish to pass the contents of cell D12 to the RIGHT function , the easiest way is to use :

Right([D12],8)

where the square brackets are a short form for Range("....") , as in :

Right(Range("D12").Value , 8)

The way you have it ,

Right("D12",8)

will return the text D12 itself , which means the Range object is going to get the complete contents of D12.

I would expect something like the following to work :

Range("D" & Rows.Count).End(xlUp).Offset(1).Select
Set x = Workbooks.Open("C:\Santa US Final.xls")
Windows("Load Times - Cumbre v1 4.xlsm").Activate
ActiveCell = Right(Format(x.Sheets("NivelSer Critical Information").Range("D12").Value, "hh:mm:ss"), 8)
Selection.NumberFormat = "hh:mm:ss"

Narayan

 

[mgoebel97]

Hi mgoebel97

Welcome to the forum..
Please upload your sample file.. You can not pass a date to a range section, It its a named range we have to trick a bit, sample file greatly appreciated.

HI,

Thank you for your reply. I figured it out yesterday after I posted this and spending some more time on it.