Copy Paste VBA formatting issues

[David Pires]

Morning

Hope you can help me.

I have a data set with the current date format
01/03/2009 (formatted to general = 39873)

Then I run the following macro:

Application.CutCopyMode = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Workbooks.Open Filename:="\\1.xlsm"
template = "1.xlsm"
Workbooks.Open Filename:="\\2.csv"
data = "2.csv"
sheet = "sheet1"
Workbooks(data).Worksheets(sheet).Cells.Copy
Workbooks(template).Worksheets("work").Range("a1").PasteSpecial Paste:=xlValues
Workbooks(data).Close

The problem is that when it pastes the data the date changes format to US on my pivot tables, very strange since when I do it manually the data format is maintained.

Anyone know what this can be?

Thank you in advance

 

[isabelle]

hi David,

try with:

Workbooks(template).Worksheets("work").Range("a1").PasteSpecial Paste:=xlPasteValues

--
isabelle




Le 2012-05-29 05:55, David Pires a écrit :

Morning

Hope you can help me.

I have a data set with the current date format
01/03/2009 (formatted to general = 39873)

Then I run the following macro:

Application.CutCopyMode = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Workbooks.Open Filename:="\\1.xlsm"
template = "1.xlsm"
Workbooks.Open Filename:="\\2.csv"
data = "2.csv"
sheet = "sheet1"
Workbooks(data).Worksheets(sheet).Cells.Copy
Workbooks(template).Worksheets("work").Range("a1").PasteSpecial Paste:=xlValues
Workbooks(data).Close

The problem is that when it pastes the data the date changes format to US on my pivot tables,

very strange since when I do it manually the data format is maintained.

 

[GS]

Try...

Dim lRows&, lCols& 'as long

With Workbooks("2.csv").Sheets("Sheet1").UsedRange
lRows = .Rows.Count: lCols = .Columns.Count
End With

Workbooks("1.xlsm").Sheets("work").Range("A1").Resize(lRows, lCols) = _
Workbooks("2.csv").Sheets("Sheet1").UsedRange

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

After rethinking my suggestion I realize that assignment of UsedRange
doesn't work. The following has been tested and works really well...

Sub CopyRange()
Dim lRows&, lCols&, vData As Variant
Dim wksTarget As Worksheet
Set wksTarget = Workbooks("2.csv").Sheets("work")
vData = Workbooks("1.xlsm").Sheets("Sheet1").UsedRange
lRows = UBound(vData): lCols = UBound(vData, 2)
wksTarget.Range("A1").Resize(lRows, lCols) = vData
Set wksTarget = Nothing
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion