Paste copied data to specified sheet based on range - variable
Hi fishy, I am having trouble determining exactly what you are trying to
do, so maybe this approach will help you to straighten it out.
Destsheet = Range("Daily Team Performance!B4")
This is incorrect syntax for assigning a string value to the variable
"Datasheet"
The correct syntax would be:
Dim Destsheet As Range
Set Destsheet = Sheets("Daily Team Performance").Range("B4").Value
--------------
Set rSource = ActiveSheet.Range("Daily Team Performance!B4:M103")
This is incorrect sytax for setting the object variable rSource.
The correct syntax would be:
1. If the ActiveSheet and Sheets("Daily Team Performance" ) are one and the
same sheet:
Set rSource = ActiveSheet.Range("B4:M103")
2. If the ActiveSheet and Sheets("Daily Team Performance") are different
sheets:
Set rSource = Sheets("Daily Team Performance").Range("B4:M103")
--------------
Set rDestination = Destsheet.Range("B4")
This is incorrect since Destsheet had been declared as a string, it cannot
be used as an object variable. If Destsheet is, in fact, a range as shown
above as corrected syntax, then this this line is not needed.
------------
rDestination.Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
If all of the previous assumptions are true then this can be changed to:
Destsheet.PasteSpecial Paste:=xlPasteValues
Hope this helps to clear things up.
"fishy" wrote in message
...
When I try FST1, I now receive an error -
Compile error: Object required
This highlights ' Set Destsheet = '
JLG, I then receive an run time 1004 at 'set rsource'
Seems to be getting there but out of my depth on what to do to resolve.
R
"JLGWhiz" wrote:
Try this:
Sub UpdateData()
Dim rSource As Excel.Range
Dim rDestination As Excel.Range
Set rSource = ActiveSheet.Range("Daily Team Performance!B4:M103")
Set rDestination = Sheets("Daily Team Performance").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
"fishy" wrote in message
...
I have the following macro that copies data from the 'Daily Team
Performance'
sheet and then dependant on the content of cell B4 on that sheet,
should
copy
this data to the respective named sheet on the 'buzz' workbook.
The problem is that when I try to run it I get the 'Invalid Qualifier'
message he Set rDestination = Destsheet.Range("B4")...
Can anyone see what I am doing wrong as the other option is screeds of
code
for every variation.
Sub UpdateData()
Dim Destsheet As String
Destsheet = Range("Daily Team Performance!B4")
Dim rSource As Excel.Range
Dim rDestination As Excel.Range
Set rSource = ActiveSheet.Range("Daily Team Performance!B4:M103")
Set rDestination = 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
.
|