Solved: Help Writing VBA code

[jo15765]

I have a workbook with 5 tabs. Tabs 1, 2 and 3 are pivot charts that Tabs 4 and 5 pull data from and they are set up like =Sheet1!J17 etc. What I am wanting to do is write a VBA macro that will open up the workbook, copy tabs 4 & 5 but ONLY the formatting and values. I have a macro that will do everything with the exception of only copy the values and formatting. The macro that I have will copy everything, whereas I need one that will only copy the values and formatting so that the links are lost. The code below is what I have that will copy the data, but every way I try to only copy formats/values errors out (compile error)


[vba]
Public Sub Move_WOrksheets
'Half Works --- Just copies more than just he values
Application.DisplayAlerts = False
Workbooks.Open Filename:="C:\Test_Data\Test.xls"
With Sheets(Array("Test_Sheet_1", "Test_Sheet_2")).Select
ActiveWindow.SelectedSheets.Copy
Application.CutCopyMode = False
ActiveWorkbook.SaveCopyAs Filename:="C:\Test_Reports\" & "Working_" & Format(Date, "mmddyyyy") & ".xls"
End With
Application.Quit
End Sub
[/vba]

[mancubus]

hi.

try this with a backup of the file.


[vba]
Public Sub Move_WOrksheets()

Dim wb As Workbook
Dim ws As Worksheet
Dim calc As Long
Dim fName As String

calc = Application.Calculation
fName = "C:\Test_Reports\" & "Working_" & Format(Date, "mmddyyyy")

With Application
.DisplayAlerts = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Set wb = Workbooks.Open("C:\Test_Data\Test.xls")

With wb
Sheets(Array("Test_Sheet_1", "Test_Sheet_2")).Copy
With ActiveWorkbook
For Each ws In .Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next
.SaveAs Filename:=fName, FileFormat:=56
.Close
End With
.Close
End With

With Application
.DisplayAlerts = True
.ScreenUpdating = True
.Calculation = calc
End With

Application.Quit

End Sub
[/vba]

[jo15765]

The code you posted above will accomplish what I was after, thank you so much for your help!!! One tweak I would like if possible....

The pivot charts refresh on open, but this macro above copies the sheets before they have time to display the updated values. Is there a way to either 1) delay the copy long enough for the two sheets to display the current values, or 2) run a macro that I recorded called "Refresh" which refreshes all queries in a workbook, and then execute your macro?

Sorry to nit pick, but again thank you for your assistance

[mancubus]

you're wellcome.

so, it's a part of a larger procedure.

because we cant see the entirety of the procedure, WAIT method may be used.

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
[VBA]
'This example pauses a running macro until 6:23 P.M. today.
Application.Wait "18:23:00"
[/VBA]

[VBA]
'This example pauses a running macro for approximately 10 seconds.
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
[/VBA]

or simply
[VBA]
Application.Wait(Now + TimeValue("0:00:10"))
[/VBA]

[jo15765]

Where in the code above would I post this statement:

Application.Wait(Now + TimeValue("0:00:10"))

[mancubus]

sorry for that.

[vba]
Public Sub Move_Worksheets()

Dim wb As Workbook
Dim ws As Worksheet
Dim calc As Long
Dim fName As String

Application.Wait(Now + TimeValue("0:00:10"))

calc = Application.Calculation
fName = "C:\Test_Reports\" & "Working_" & Format(Date, "mmddyyyy")

With Application
.DisplayAlerts = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

[/vba]

adjust the time needed for updationg the pivots...

[jo15765]

That worked to perfection. Thank you.

[mancubus]

you're wellcome.

pls mark the thread as SOLVED from thread tools.