Set destination sheet based on variable and paste data assistance

F

fishy

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

--------------------------------------------------------------------------------------------
 
J

joel

try these changes

Sub Teamexports()

'Team1
with Thisworkbook.Sheets("Teamexports")
.Range("C3") = .Range("C5")
end with
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

with thisworkbook
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"

Set Teambk = Workbooks.Open( Filename:=OpenName
UpdateLinks:=False)
Call Update_Data2(Teambk)
end with
End Sub

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

Sub Update_Data2(Teambk)

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

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

Set rSource = Teambk.sheets("Daily Tea
Performance").Range("B4:M103")
Set rDestination = .Sheets("Destsheet").Range("B4")

rSource.Copy
rDestination.PasteSpecial Paste:=xlPasteValues
end with


End Sub
 
J

JLGWhiz

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 said:
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
Click to expand...
 
F

fishy

Still giving a debug 'Compile error: Object required' at the set Destsheet
and is highlighting the word 'Destsheet'.

JLGWhiz said:
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 said:
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
Click to expand...


.
Click to expand...
 
F

fishy

Joel,

This is giving a debug on Update_Data on the following:

Compile error: Method or data member not found'

With ThisWorkbook
Summary = .Range

and is highlighting the word 'range'

Any clues
 
J

joel

I forgot there are some properties that don't work with Thisworkboo
and do work with Activeworkbook. I didn't wnat to use Activeworkboo
becuse when you open a workbook the focus chabges to the workbook tha
was opened which is the cuawe of your problems. I made some mino
changes. see if this works


Sub Teamexports()

'Team1
With ThisWorkbook.Sheets("Teamexports")
.Range("C3") = .Range("C5")
End With
Call Update_Data

Exit Sub

End Sub

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

With Workbooks(ThisWorkbook.Name)
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"

Set Teambk = Workbooks.Open(Filename:=OpenName, UpdateLinks:=False)
Call Update_Data2(Teambk)
End With
End Sub


Sub Update_Data2(Teambk)

With Workbooks(ThisWorkbook.Name)

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

Set rSource = Teambk.Sheets("Daily Tea
Performance").Range("B4:M103")
Set rDestination = .Sheets("Destsheet").Range("B4")

rSource.Copy
rDestination.PasteSpecial Paste:=xlPasteValues
End With


End Sub
 
F

fishy

Progress, but new debug at Update_Data2

Run-time error '9':
Subscript out of range

This is highlighting the row

Set rDestination = .Sheets("Destsheet").Range("B4")

Couldnt see where we have told it what 'Destsheet' is?

R
 
J

JLGWhiz

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 said:
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 said:
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
Click to expand...
Click to expand...
 
J

joel

The line of code was from your original posted code. The name of th
sheet should match the TAB name of the sheet in the workbook. since
don't have your workbook I had to assume the sheet name was correct.

"Destsheet" (no double quotes) should be a tab in the same workboo
where the macro is located. Make sure the are no extra spaces in th
TAB name.
 
F

fishy

Debugging at

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

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

R

JLGWhiz said:
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 said:
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 said:
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
Click to expand...
Click to expand...


.
Click to expand...
 
F

fishy

Hi,

Destsheet is the destination sheet based on the range of the copied sheet
cell B4 i.e. if this range says Sheet1 then the data is pasted into Sheet1,
If it says Sheet2 then it copies into Sheet2 etc.

R
 
F

fishy

Finally sorted 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
 
F

fishy

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 said:
Debugging at

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

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

R

JLGWhiz said:
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 said:
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





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
Click to expand...


.
Click to expand...
Click to expand...
 
V

Vacuum Sealed

Not being an Excel guru Parse, and I maybe mistaken, but I though there was
a difference in how your executed Functions and Sub() Routines.

As in:

Call MyFunction

or

Application.Run "MySub"

In this section of your code you have

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

As you have declared this as a Sub Routine and not a Function, would you not
then execute it with:

Application.Run "Update_Data"

Again, not being absolutley sure, but it may have some bearing...

HTH
Mick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Paste copied data to specified sheet based on range - variable iss 6
Filter based on named range cell - debug help required 3
Filter worksheet based on named range on alternate sheet 3
Code to copy column in sheet 1 to column in sheet 2 based on matched criteria 0
Copy worksheet, trying to paste to new workbook error assistance. 1
Macro to copy paste values based on cell input ... 4
Marco to copy and paste values based on cell value (cont) 6
Selecting Range of Copied Text 2

Top