Copy Named Range in Excel VBA

[fourty]

How can i copy data from a named range in a different worksheet and paste it to another worksheet all done without activating both sheet. With all code just behind a form button

If i try to use the statement application.goto reference:="x" where x is the named range but this activates the sheets
if i try to use the statement range("workbookname!x") where x is the named range, i get an exception

Anyone please?

[Stuugie]

Are there many named ranges in the one sheet that you want the data from? I'm personally not a fan of working with named ranges in code, for these reasons too. The goto code will always take you to the range so that cannot be used. At any rate, the following can be amended to your purposes:

Sub CopyNamedRangeData()

    Dim wS3 As Worksheet
    Dim rCnt As Long, cCnt As Long

    Application.ScreenUpdating = False
    Set wS3 = Worksheets("Sheet3")
    Application.Range("NamedRange").Copy
    wS3.Range("A1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

[fourty]

Thank you Stuugie,

But then again i need to clear another range where i paste the data and do some sorting on it, then copy the sorted data to another sheet which i should now activate to display these results..

i have named ranges in severeal different wksheets so to speak and if the rcnt and ccnt will work fine without activating these sheets(where the named ranges exist) then ill buy.

First let me try it out. Thanks alot

[Stuugie]

Hey fourty, I was playing around with row counts and column counts with the named ranges but with no success. Those variables can be disregarded as I meant to remove them.

As for clearing other ranges, it is tough from my point of view to help as I cannot see your sheets. I can help without seeing your data if and only if all the data on that sheet can be cleared.

Also, do you really need a second sheet to sort and a third to display the sorted data? Seems like an extra step to me.

[fourty]

yeah i need all three sheets.

im working on a school mgt system and since most of my local schools are conversant with excel, i did it in excel. the file is abit large as it contains test data

please gve me your mail so i can share the file from my google drive.

i was somewhat successful trying out the code on a new workbook without many sheets ill post the code in a while.

[fourty]

Ok, this code works well in a new wkbook whichever sheet i place the button and the screen doesnt flicker at all

Sub Button5_Click()
Dim a, b, c
Dim x
Set a = Range("[book1.xlsm]sheet1!myrange")
Set b = Sheet3.Cells(13, 9)
Set c = Sheet2.Cells(13, 7)
a.Copy (b)
x = b.Value * 100
c.Value = x
End Sub

my defined range there (myrange) is in sheet1!e14:e19.

[Stuugie]

Hi fourty, did you still need my email or do you have it all figured out?

[fourty]

Ok, after hours of tearing out myself i realise all i needed to cure my problem was in line 6 of your first reply.

Thank you still. Its on my part now to see how to make it all smooth. Thanks alot Stuugie for offering assistance.

[Stuugie]

You are most welcome and good luck with the rest of your project. If you run into any other issues, don't hesitate to post or even send me a message to help.