Set destination sheet based on variable and paste data assistance
 

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

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

Set destination sheet based on variable and paste data assistance
 

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 Team
Performance").Range("B4:M103")
Set rDestination = .Sheets("Destsheet").Range("B4")

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


End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=195120

http://www.thecodecage.com/forumz

Set destination sheet based on variable and paste data assistance
 

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

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

Set destination sheet based on variable and paste data assista
 

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

"JLGWhiz" wrote:

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

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



.

Set destination sheet based on variable and paste data assista
 

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


"joel" wrote:


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 Team
Performance").Range("B4:M103")
Set rDestination = .Sheets("Destsheet").Range("B4")

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


End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=195120

http://www.thecodecage.com/forumz

.

Set destination sheet based on variable and paste data assistance
 

I forgot there are some properties that don't work with Thisworkbook
and do work with Activeworkbook. I didn't wnat to use Activeworkbook
becuse when you open a workbook the focus chabges to the workbook that
was opened which is the cuawe of your problems. I made some minor
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 Team
Performance").Range("B4:M103")
Set rDestination = .Sheets("Destsheet").Range("B4")

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


End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=195120

http://www.thecodecage.com/forumz

Set destination sheet based on variable and paste data assista
 

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



"joel" wrote:


I forgot there are some properties that don't work with Thisworkbook
and do work with Activeworkbook. I didn't wnat to use Activeworkbook
becuse when you open a workbook the focus chabges to the workbook that
was opened which is the cuawe of your problems. I made some minor
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 Team
Performance").Range("B4:M103")
Set rDestination = .Sheets("Destsheet").Range("B4")

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


End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=195120

http://www.thecodecage.com/forumz

.

Set destination sheet based on variable and paste data assistance
 

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

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

Set destination sheet based on variable and paste data assistance
 

The line of code was from your original posted code. The name of the
sheet should match the TAB name of the sheet in the workbook. since I
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 workbook
where the macro is located. Make sure the are no extra spaces in the
TAB name.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=195120

http://www.thecodecage.com/forumz

Set destination sheet based on variable and paste data assista
 

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

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





.

Set destination sheet based on variable and paste data assista
 

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

"joel" wrote:


The line of code was from your original posted code. The name of the
sheet should match the TAB name of the sheet in the workbook. since I
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 workbook
where the macro is located. Make sure the are no extra spaces in the
TAB name.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=195120

http://www.thecodecage.com/forumz

.

Set destination sheet based on variable and paste data assista
 

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

"fishy" wrote:

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

"joel" wrote:


The line of code was from your original posted code. The name of the
sheet should match the TAB name of the sheet in the workbook. since I
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 workbook
where the macro is located. Make sure the are no extra spaces in the
TAB name.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=195120

http://www.thecodecage.com/forumz

.

Set destination sheet based on variable and paste data assista
 

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

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





.

Set destination sheet based on variable and paste data assista
 

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