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

V

velocity1234

New Member
Joined
May 5, 2014
Messages
25
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,
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Try This:
Code: 
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
 
Upvote 0
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?
 
Upvote 0
I need to know what sheet you want to copy the data from and
What sheet do you want to paste the data on?
 
Last edited:
Upvote 0
velocity1234 said:
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?
Click to expand...

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.

Code: 
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.

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 = Selection
rngSource.Copy rngDest
End Sub
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
Try this:

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 = 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.
 
Upvote 0
If you just want to paste values then a small modification is needed:

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 = Range("C" & Rows.Count).End(xlUp).Offset(1)
rngSource.Copy
rngDest.PasteSpecial xlPasteValues
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Counter in For Each loop
Replies
2
Views
182
Possum17
P
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
298
Azamamir
A
J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
156
shknbk2
S
C
  • Question
Calculations require application restart
Replies
2
Views
143
cpupartsplz
C
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
165
pennbowl
P

Forum statistics

Threads
1,214,588
Messages
6,120,409
Members
448,959
Latest member
camelliaCase

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top