Copying Ranges from One Worksheet to another

[steveh8204]

I read on a website tutorial that a more efficient way of copying and pasting ranges was to use the following:

' This is faster
Range("A1:A10").Value = Range("B1:B10").Value

' This is slower
Range("B1:B1").Copy Destination:=Range("A1:A10")

I was led to believe this way made the code run significantly quicker, the problem I've got though is that I'm copying from a different spreadsheet so I was wondering whether this is still possible to do?

The code I am using to copy and paste is:

    Worksheets("SAP").Activate
    Dim wb As Workbook
    Dim myfilename As String
    myfilename = "P:\PFMSHARE\LOGISTICS\STOCK CHECK DOCUMENTS & BBE CHECKER\Macs Auto Stock Checker\Macs All.MHTML"
    Set wb = Workbooks.Open(myfilename)
    a = Range("A2:d200").Copy
    Workbooks("Macs Auto Stock Check.xlsm").Activate
    Range("a3").Select
    paste = a
     Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A3").Select
    Sheets(2).Activate

With the Macs All.MHTML file being another spreadsheet to the one currently open.

 

[DanteAmor]

Try with this:

    Dim wb As Workbook
    Dim myfilename As String
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Application.ScreenUpdating = False
    Set ws1 = Sheets("SAP")
    myfilename = "P:\PFMSHARE\LOGISTICS\STOCK CHECK DOCUMENTS & BBE CHECKER\Macs Auto Stock Checker\Macs All.MHTML"
    
    Set wb = Workbooks.Open(myfilename)
    Set ws2 = wb.Sheets(1)
    ws1.Range("A3:D201").Value = ws2.Range("A2:D201").Value
        
    wb.Close False

 

[steveh8204]

Thanks, I'll try it out in the morning.

 

[steveh8204]

Try with this:

    Dim wb As Workbook
    Dim myfilename As String
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Application.ScreenUpdating = False
    Set ws1 = Sheets("SAP")
    myfilename = "P:\PFMSHARE\LOGISTICS\STOCK CHECK DOCUMENTS & BBE CHECKER\Macs Auto Stock Checker\Macs All.MHTML"
    
    Set wb = Workbooks.Open(myfilename)
    Set ws2 = wb.Sheets(1)
    ws1.Range("A3:D201").Value = ws2.Range("A2:D201").Value
        
    wb.Close False

Nice one, runs a lot faster now. Had to get my head around it a bit to understand what to change and which code to still keep in but worth it. And I've learnt a bit more.

Thanks for your help.

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.