vba copy paste in one worksheet, while being in a different worksheet

[velocity1234]

I have several sheets in a work book. I want to copy and paste a range of cells in a different sheet even if I am not on that sheet. For example lets say I am on sheet 1 when the macro fires off. I don't want to jump to the worksheet "weeklypricedata" but remain on sheet 1. This is what I have and excel gets hung up on the red text.

Sub PasteGraphData()
'
Application.ScreenUpdating = False


Worksheets("WeeklyPriceData").Range("GraphData").Select
Selection.Copy

Range("C" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

End Sub

Where am I going wrong? Any help would be greatly appreciated.

Thanks,

 

[My Aswer Is This]

Try This:

Sub PasteGraphData()
Application.ScreenUpdating = False
Worksheets("WeeklyPriceData").Range("GraphData").Copy
Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

 

[velocity1234]

I tried that before. The problem with that code is that it will paste the data on the sheet you happen to be on and destroy whatever data is in the paste range. The code needs to go "weeklypricedata" sheet to do the paste.

Any other ideas?

 

[My Aswer Is This]

I need to know what sheet you want to copy the data from and
What sheet do you want to paste the data on?

 

[Harry Flashman]

I tried that before. The problem with that code is that it will paste the data on the sheet you happen to be on and destroy whatever data is in the paste range. The code needs to go "weeklypricedata" sheet to do the paste.

Any other ideas?

Try assigning your worksheets and ranges to object variables. For example this code will copy a selected range (regardless of what worksheet that range is on) and paste in your destination range.

Sub test()
Dim ws As Worksheet
Dim rngDest As Range
Set ws = Worksheets("WeeklyPriceData")
Set rngDest = ws.Range("GraphData")
Selection.Copy rngDest
End Sub

Edit:

I just realized you want it the other way around. This code copies your range GraphData and pastes to the selected cell, regardless of which worksheet is the active worksheet.

Sub test()
Dim ws As Worksheet
Dim rngSource As Range, rngDest As Range
Set ws = Worksheets("WeeklyPriceData")
Set rngSource = ws.Range("GraphData")
Set rngDest = Selection
rngSource.Copy rngDest
End Sub

 

[velocity1234]

they are the same sheet. "weeklypricedata"

 

[velocity1234]

ok, How do I tie in your code

Sub test()Dim ws As Worksheet
Dim rngSource As Range, rngDest As Range
Set ws = Worksheets("WeeklyPriceData")
Set rngSource = ws.Range("GraphData")
Set rngDest = Selectionrng
Source.Copy rngDest
End Sub

into my code:

Application.ScreenUpdating = False

Range("C" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

End Sub

I'm a little lost there, because my code is dropping down 1 row at a time as it is pasting the data.

 

[Harry Flashman]

Try this:

Sub test()
Dim ws As Worksheet
Dim rngSource As Range, rngDest As Range
Set ws = Worksheets("WeeklyPriceData")
Set rngSource = ws.Range("GraphData")
Set rngDest = Range("C" & Rows.Count).End(xlUp).Offset(1)
rngSource.Copy rngDest
End Sub

Just be aware that we have not fully qualified the destination range, so the data will be pasted into whichever worksheet is active at the time.

 

[Harry Flashman]

If you just want to paste values then a small modification is needed:

Sub test()
Dim ws As Worksheet
Dim rngSource As Range, rngDest As Range
Set ws = Worksheets("WeeklyPriceData")
Set rngSource = ws.Range("GraphData")
Set rngDest = Range("C" & Rows.Count).End(xlUp).Offset(1)
rngSource.Copy
rngDest.PasteSpecial xlPasteValues
End Sub