Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste copied data to specified sheet based on range - variable iss
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste copied data to specified sheet based on range - variable iss
hi
syntax problem. try this. Set rDestination = sheets("Destsheet").Range("B4") regards FSt1 "fishy" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste copied data to specified sheet based on range - variable
This has allowed a progression but now I am getting an error:
'Run time error 1004': Method 'Range' of object' _Global' failed This occurs at line: Destsheet = Range("Daily Team Performance!B4") The only thing I can think of is that the 'Daily team performance' is in a variable workbook that is opened and is the active book using the following code: '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 Do I need to refer back to these variables for the sheets? R |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste copied data to specified sheet based on range - variable
hi.
again styntax problem Set Destsheet =sheets("Daily Team Performance"). Range("B4") and objects have to be set. regards FSt1 "fishy" wrote: This has allowed a progression but now I am getting an error: 'Run time error 1004': Method 'Range' of object' _Global' failed This occurs at line: Destsheet = Range("Daily Team Performance!B4") The only thing I can think of is that the 'Daily team performance' is in a variable workbook that is opened and is the active book using the following code: '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 Do I need to refer back to these variables for the sheets? R |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste copied data to specified sheet based on range - variable iss
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste copied data to specified sheet based on range - variable
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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting Range Based on Cell Content then Copy/Paste to New Sheet,Looping | Excel Programming | |||
Paste cells copied from another sheet | Excel Discussion (Misc queries) | |||
Assign copied range to a variable | Excel Programming | |||
Copy&Paste variable range based on cell value | Excel Programming | |||
Finding a named range based on cell value and copy/paste to same sheet? | Excel Programming |