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

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





.