Sheet naming in Macros

[applebyd]

Hi all,

Looks like I've finally got a Vba query!

I've a workbook that is updated and renamed on a weekly basis.

I've recorded a Macro that takes 3 sheets from it and pastes them
into a new workbook, the first section of the code is shown below.

This opens a new book and re-names it TEMP.xls then starts to copy data
and reformat it.

At the moment it works fine as long as it's run from the Workbook

Rep Gen TEST BASE.xls (The Dev Version.)

The macro itself works perfectly however it will obviously not work
if I rename the workbook.

What I need is some advice/help on what I need to do to change the macro so it works from the report it's called from not just the DEV copy.

I think I need to change the line I've bolded out below but am not sure.

Any help greatly appreciated


Workbooks.add
ActiveWorkbook.SaveAs Filename:= _
"S:CUSTCAREResource Unitphone statsWeekly Stats6 Week SummariesRolling ReportsTEMP.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Windows("Rep Gen TEST BASE.xls").Activate
Sheets("RAW DATA ENTRY").Select
Range("C3:P69").Select
Range("P69").Activate
Selection.Copy
Windows("TEMP.xls").Activate
ActiveSheet.Paste
Columns("A:A").ColumnWidth = 13.9
ActiveWindow.Zoom = 70
Columns("N:N").ColumnWidth = 16.1
Columns("N:N").Select
Columns("M:M").ColumnWidth = 16.4
Range("P28").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "6 Week Overall"
Range("D35").Select
Sheets("Sheet2").Select
Windows("Rep Gen TEST BASE.xls").Activate
Sheets("Team Raw Data").Select
Range("B3:H49").Select
Application.CutCopyMode = False
Selection.Copy
Windows("TEMP.xls").Activate


Thanks

DaveA
This message was edited by applebyd on 2002-10-10 09:22
This message was edited by applebyd on 2002-10-10 09:23

 

[Richie(UK)]

Hi Dave,

Try the following (untested!) routine.

<pre>
Sub copy()

'turn off screen updating to prevent flicker during macro
Application.ScreenUpdating = False

'create the destination workbook
Workbooks.Add
ActiveWorkbook.SaveAs FileName:= _
"S:CUSTCAREResource Unitphone statsWeekly Stats6 Week SummariesRolling ReportsTEMP.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'perform the copy process from the workbook containing this routine
ThisWorkbook.Worksheets("RAW DATA ENTRY").Range("C3:P69").copy _
Destination:=ActiveWorkbook.Worksheets("Sheet1").Range("A1")

'format the sheet
Columns("A:A").ColumnWidth = 13.9
ActiveWindow.Zoom = 70
Columns("N:N").ColumnWidth = 16.1
Columns("N:N").Select
Columns("M:M").ColumnWidth = 16.4

'clear the clipboard
Application.CutCopyMode = False

Application.ScreenUpdating = True

End Sub</pre>

HTH

 

[applebyd]

Thanks Ritchie,

Worked a treat.

For some reason I needed to re declare the range for the paste AFTER the destination select but everything else was fine.

I'm gobsmacked by the 20 min response time!

After cutting it into the code I had in 6 places and testing the whole thing took less than 2 hrs.

Thanks again.

DaveA