Help with VBA Macro pulling from one workbook to another, not pulling the date in desired format

[Viva_Excel]

Hi,

We have one master workbook that we need to pull in data from many other workbooks using vba macro coding. The data from each of the other workbooks is all in the same range and format. HOWEVER, the problem when copying the date from the many workbooks into the master workbook is that it copies exactly what is shown from the source workbooks and not the true value we need. For example, the source work book has the date of 1/1/2014 but the format in these workbooks has the date shown as "Jan-14" (MMM-YY). When the macro goes out and pulls this data, instead of it pasting in the true value of the date "1/1/2014", it is pulling exactly what is shown from the source workbook which is "Jan-14", but the master workbook is interpreting this as "14-Jan" or 1/14/16 (DD/MM/YY).

Also, we are having a similiar problem with the numbers with decimals. The source workbook is pulling in the shown formatted value, not the true value. For example, the true value in the cell is 7.4634, but we have it formatted to be "7.5" (one decimal place). We want it to pull the full value into the master workbook but when it pastes it into the master workbook, it is only pasting the shown value of "7.5" and not 7.4634.

please let us know if you can help. Thanks so much!

 

[JoeMo]

You should be able to coerce the pulled data to arrive at what you want. For example, if you want a date formatted as "MMM-YY" in cell A1 of your source workbook to be returned as "mm/dd/yy" you could use:

CDate(CDbl(Range("A1").Value))

provided the "date" in your source workbook is a number and not text.

If you post your code or at least the parts that are causing the trouble, someone here might be able to provide modifications for you.

 

[Viva_Excel]

Thanks so much, JoeMo. Here is the code. Since we are VERY novice with VBA coding and have been trying to piece together different types of code, we are not exactly sure where to put the code you have mentioned in this script. Can you help let us know where we should paste this (see script below

Sub CashFlow_Consolidation()

Dim FolderPath As String, FilePath As String, Filename As String

FolderPath = "C:\OurData\Execution Data\Consolidation"

FilePath = FolderPath & "*.xls*"

Filename = Dir(FilePath)

Dim lastrow As Long, lastcolumn As Long

Do While Filename <> ""

Workbooks.Open (FolderPath & Filename), Password:="hixxxbye", UpdateLinks:=0

Sheets("Chart Data").Select

ActiveSheet.Range("$A:$Z").AutoFilter Field:=1

'Find the last row in the source data document
lastrow = Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

lastcolumn = Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column

Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close

erow = Sheet1.Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Offset(1, 0).Row

Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 27)).PasteSpecial Paste:=xlPasteAll


Filename = Dir

Loop

Application.DisplayAlerts = False
End Sub

 

[JoeMo]

Looking at your code, with no understanding of the layout of your source workbooks, I'm guessing that the data you are having format trouble with is part of this copy:

Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy

If that is correct, can you tell me which columns in the copied range hold the problematic data, and exactly what type of data is in each column?

Also, regarding the date data, are the dates numbers or text?