Excel 2010 In .xls (97-2003) Workbook - Run Code To Copy From Another Open Workbook 1

[PWD]

Good afternoon, in the code below the commented out lines are what I had hoped would work but result in this:-

"Run-time error '438':
Object doesn't support this property or method"

At the moment, I'm having to settle for the rest.

Sub MARPROV_Values()
Dim x As Integer

For x = 3 To 58

Application.ScreenUpdating = False
Windows("MARPROV.xls").Activate
'' Windows("MARPROV.xls").Sheets(x).Range("J11:W11").Copy
    Sheets(x).Range("J11:W11").Copy
    Windows("MARPROV Template.xls").Activate
'' ActiveWorkbook.Sheets(x).Range("J11").PasteSpecial (xlPasteValues)
 Sheets(x).Range("J11").PasteSpecial (xlPasteValues)

Next x

Application.ScreenUpdating = True
End Sub

What can I do to stop 'having to' "Activate"?

Many thanks,
D€$

 

[SkipVought]

HI,

Just use a valid reference and you will seldom need to Activate/Select an object.

I would not use a Windows() reference; rather a Workbooks() reference. You might want to Set a workbook object for each to streamline the code.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PWD]

Hi Skip, thanks. I've ended up with:

Sub MARPROV_Values()

Dim x As Integer
Dim wb1 As String
Dim wb2 As String

wb1 = "MARPROV.xls"
wb2 = "MARPROV Template.xls"

For x = 3 To 58

Application.ScreenUpdating = False

    Workbooks(wb1).Sheets(x).Range("J11:W11").Copy
    Workbooks(wb2).Sheets(x).Range("J11").PasteSpecial (xlPasteValues)

Next x

Application.ScreenUpdating = True
End Sub

It takes a couple of seconds; I can live with that!

Many thanks,
D€$

 

[PWD]

Slight modification as I seem to be adding more wroksheets:

Sub MARPROV_Values()

Dim x As Integer
Dim wb1 As String
Dim wb2 As String
[b] [i]Dim LastSheet As Integer[/i][/b]

wb1 = "MARPROV.xls"
wb2 = "MARPROV Template.xls"

[b][i]LastSheet = Workbooks(wb1).Sheets.Count - 4

For x = 3 To LastSheet[/i][/b]

.......

Many thanks,
D€$