Range gets pasted as a Date

[dgroman]

So I'm working with VBA to copy and past pivot talbes into a new worksheet. I have several different pivot tables that are being pasted into the new worksheet, one of which has some ranges 0-4, 5-9,10-14 and 15+. When it pastes 5-9 and 10-14, it keeps coming up as May 9 and October 14, how do I fix this? I've reformatted the cells each time but it still either comes up with that date or the code for that date but not the range. Yet the pivot table has the proper ranges with no problems. Please help....

 

[Jonmo1]

Welcome to the board.

Try formatting the destination cells as text BEFORE pasting the new value..

 

[Weaver]

You could try formatting the target cells as 'text' before pasting the data to them. I think this ought to work.

HTH

 

[dgroman]

See thats what I've been trying to do but it just doesn't fix the problem its really annoying. I've never had this problem before, is there a way to write it into the code so that it exludes information in date form?

 

[Norie]

What code are you currently using for this?

 

[dgroman]

Call insert_exhibits("A25:O33", "N2:AB10", "P&L-1", "Slide 3 ")
[code]
 
 
Thats the last line of the code where it takes the pivot table from my first worksheet and pastes it into the other worksheet.

 

[Norie]

Where's the rest of the code?

I think there just might be a sub called 'insert_exhibits' floating about somewhere.

PS Are you trying to prepare Excel data for PowerPoint slides?

 

[dgroman]

Sub insert_exhibits(source_range As String, output_range As String, source_worksheet As String, output_worksheet As String)
'Set xl = CreateObject("Excel.Application")
Source = ThisWorkbook.Name
file_path = ThisWorkbook.Path
Output_File = "P&L Exhibits - In Progress.xls"
'Workbooks.Open Filename:=Output_File
Visible = True
Workbooks(Output_File).Sheets(output_worksheet).Range(output_range) = _
Workbooks(Source).Sheets(source_worksheet).Range(source_range).Value
End Sub
[code]
 
 
Sorry thats the code that has to deal with the pasting code I sent you before.

 

[Jonmo1]

ok, it's not actually copy/pasting...it's doing A=B

Try Changing this

Workbooks(Output_File).Sheets(output_worksheet).Range(output_range) = _
Workbooks(Source).Sheets(source_worksheet).Range(source_range).Value

to

With Workbooks(Output_File).Sheets(output_worksheet).Range(output_range)
    .NumberFormat = "@"
    .Value = Workbooks(Source).Sheets(source_worksheet).Range(source_range).Text
End With

 

[dgroman]

About the power point slides, I don't believe so.