Finally got to the bottom of it:

Sub Update_Data()

'Set functions
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Declare names
Dim datestamp As String
Dim Namefile As String
Dim OpenName As String
Dim Summary As String
Dim rSource As Excel.Range
Dim Destsheet As Range
Dim MySh As Variant
Dim MyWk As Variant

'Clear ranges
Set rSource = Nothing
Set Destsheet = Nothing
Set MySh = Nothing
Set MyWk = Nothing

Summary = Range("TeamData") & " Performance Model WC " &
Format(Range("WCDATA"), "dd_mm_yy") & ".xls"
datestamp = Range("TeamData") & " Performance Model WC " &
Format(Range("WCDATA"), "dd_mm_yy")

'open the workbook
Namefile = Range("TeamData")
'enter file path
OpenName = "\\ngclds06\manops\ams\Service\POM\" & Namefile & "\Performance
Models\" & datestamp & ".xls"
Workbooks.Open Filename:=OpenName, UpdateLinks:=True

Setworkbook = datestamp
Sheets("Daily Team Performance").Select
Set rSource = ActiveSheet.Range("B4:M103")
Set Destsheet = ActiveSheet.Range("D4")

rSource.Copy

Windows("Buzz.xls").Activate
MySh = Destsheet
Sheets(MySh).Select

Range("B4:M103").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("A1").Select
Application.CutCopyMode = False

Sheets("Control").Select

MyWk = Summary
Windows(MyWk).Activate
ActiveWorkbook.Close

End Sub

"fishy" wrote:

Debugging at

Set rSource = ActiveSheet.Range("Daily Team Performance!B4:M103")

'Run-time error 1004: Application defined or object defined error'

R

"JLGWhiz" wrote:

Forgot to change the Dim statement:

Sub Update_Data2()

Dim Destsheet As Range
Set Destsheet = Sheets("Daily Team Performance").Range("B4")
Dim rSource As Excel.Range
Set rSource = ActiveSheet.Range("Daily Team Performance!B4:M103")
rSource.Copy
Destsheet.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

Range("A1").Select

Application.CutCopyMode = False

valKill:
Set rSource = Nothing
Set Destsheet = Nothing

Exit Sub

End Sub







"JLGWhiz" wrote in message
...
some stuff that appeared to be superfluous was eliminated. Try this:

Sub Update_Data2()

Dim Destsheet As String
Set Destsheet = Sheets("Daily Team Performance").Range("B4")
Dim rSource As Excel.Range
Set rSource = ActiveSheet.Range("Daily Team Performance!B4:M103")
rSource.Copy
Destsheet.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

Range("A1").Select

Application.CutCopyMode = False

valKill:
Set rSource = Nothing
Set rDestination = Nothing

Exit Sub

End Sub





"fishy" wrote in message
...
I have an excel book that works through each of the teams based on a range
on
the control sheet (Teamexports), opens its respective team file based on
the
date and filepath (Update_Data) and then I want it to copy the data to
the
named team tab already in place based on the value in the copied sheets
range
[B4] (Update_Data2).


The first two elements work fine but the Update_Data2 keeps debugging due
to
objects etc.

I posted before and got assistance but have got back from a few days off
and
need to get it operational.

Detailed below is the code if anyone could help in resolving and/or
streamlining.


--------------------------------------------------------------------------------------------

Sub Teamexports()

'Team1
Range("C5").Select
Selection.Copy
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Call Update_Data

Exit Sub

''Team2, etc etc,

--------------------------------------------------------------------------------------------

Sub Update_Data()

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

'collate the name of the files
Dim datestamp As String
Dim Namefile As String
Dim OpenName As String
Dim Summary As String

Summary = Range("TeamData") & " Performance Model WC " &
Format(Range("WCDATA"), "dd_mm_yy") & ".xls"
datestamp = Range("TeamData") & " Performance Model WC " &
Format(Range("WCDATA"), "dd_mm_yy")
'open the workbook

Namefile = Range("TeamData")
OpenName = "\\ngclds06\manops\ams\Service\POM\" & Namefile &
"\Performance
Models\" & datestamp & ".xls"

Workbooks.Open Filename:=OpenName, UpdateLinks:=False

Call Update_Data2

End Sub

--------------------------------------------------------------------------------------------

Sub Update_Data2()

Dim Destsheet As String
Set Destsheet = Sheets("Daily Team Performance").Range("B4")

Dim rSource As Excel.Range
Dim rDestination As Excel.Range

Set rSource = ActiveSheet.Range("Daily Team Performance!B4:M103")
Set rDestination = Sheets("Destsheet").Range("B4")

rSource.Copy
rDestination.Select

Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

Range("A1").Select

Application.CutCopyMode = False

valKill:
Set rSource = Nothing
Set rDestination = Nothing

Exit Sub

End Sub

--------------------------------------------------------------------------------------------





.