0

I have the code seen below to copy an unknown number of rows (rows sometimes are Up to 20k, columns only (6)) and paste it into a different workbook. But it is running extremely slow Screen Updating, Calculation Modes, Enable Events. No change. pleases help

Sub CopyData()
Dim sh1 As Worksheet
Dim ShData As Worksheet
Dim sh5 As Worksheet
Dim LR As Long
Dim rng As Range
ThisWorkbook.Worksheets("Destnation").Activate
Set ShData = Workbooks("Data.xlsx").Worksheets(2)
Set sh1 = ThisWorkbook.Worksheets("Sheet1")
Set sh5 = ThisWorkbook.Worksheets("Destnation")

LR = ShData.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = ShData.Range("A2:A" & LR)
rng.EntireRow.Copy sh5.Range("A2")
sh1.Range("H1").Value = Workbooks("Data.xlsx").Worksheets(2).Name
End Sub

thanks

Improve this question
2
  • since you're putting timed results to each answer (which is interesting), why not tell everyone what you're STARTING benchmark was? "Extremely slow" is too subjective. Commented Nov 15, 2020 at 18:28
  • Yoy are right ,,,, code ran in : 27.85 seconds Commented Nov 15, 2020 at 18:44

4 Answers 4

Reset to default
2

If you're interested in values only, you can use .Value property of Range object, sizing the paste range appropriately

Sub CopyData()

    Dim sourceRng  As Range
    Dim sourceSheetName As String
    
    With Workbooks("Data.xlsx").Worksheets(2)
        Set sourceRng = .Range("A2", .Cells(Rows.Count, 1).End(xlUp)) ' set the source range
        sourceSheetName = .Name
    End With
    
    With ThisWorkbook
        .Worksheets("Destnation").Range("A2").Resize(sourceRng.Rows.Count).Value = sourceRng.Value
        .Worksheets("Sheet1").Range("H1").Value = sourceSheetName
    End With
    
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

this doesnt copy an unknown number of rows
@ahmedemm yes it does.
Sorry, this is a pretty solid answer too. I didn't see an array and got dismissive. Solid so I 👍
@PGSystemTester, "Sorry" for what? And thanks for your comment.
@user3598756 oh, I put a couple comments in my post and below that FaneDu had best answer if values were the only consideration. i'll update my answer. Mine only adds value if OP truly wants all features of a copy paste (i.e. formatting... etc).
|
1

Please, try the next code. No need to activate, select and clipboard using. No need to copy entire row, too. But it will not copy the format:

Sub CopyDataCC()
 Dim sh1 As Worksheet, ShData As Worksheet, sh5 As Worksheet
 Dim LR As Long, LCol As Long, arrCopy

 Set ShData = Workbooks("Data.xlsx").Worksheets(2)
 Set sh1 = ThisWorkbook.Worksheets("Sheet1")
 Set sh5 = ThisWorkbook.Worksheets("Destnation")

 LR = ShData.cells(rows.count, 1).End(xlUp).row
 LCol = ShData.cells(2, Columns.count).End(xlToLeft).Column

 arrCopy = ShData.Range("A2", ShData.cells(LR, LCol))
 sh5.Range("A2").Resize(UBound(arrCopy), UBound(arrCopy, 2)).Value = arrCopy
 sh1.Range("H1").Value = Workbooks("Data.xlsx").Worksheets(2).Name
End Sub
Improve this answer

4 Comments

This is the best answer so long as you don't care about formatting/etc. I gave an alternative method if you truly do want COPY/PASTE. This answer grabs values in a clean way which is probably what you actually want.
@FaneDuru I think OP may have been so floored by the improvement in speed that that's all he could type before passing out in disbelief to bearing witness to your sorcery.... 🙂
FaneDuru,, Thank you for your contribution
@Ahmed: Glad I could help!
0

delete entirerow property from copy an replace

Set rng =shData.Range(ShData.cells(2,1),ShData.cells(LR,ShData.cells(1,columns.count).xl(toleft).column))

Improve this answer

3 Comments

I get this :run time error 438 object doesn't support this property or method and rng = nothing
sorry my bad. should be Set rng =shData.Range(ShData.cells(2,1),ShData.cells(LR,ShData.cells(1,columns.count).end(xltoleft).column))
it works if u have header on sheet shData. if u dont have header below Set rng =shData.Range(ShData.cells(2,1),ShData.cells(LR,ShData.cells(2,columns.count).end(xltoleft).column))
0

Both Fandune and user3598756 are optimal if you are only concerned with values, which is probably the case. However you didn't specify that in your post, so I'll add my answer for anyone who truly needs to capture format (perhaps with a date?)

Try this below and also note commentary on some of your code.

Sub CopyData()
'This macro is running from the file where the data is being pasted.


Dim ShData As Worksheet 'this file is presumed to be open
    Set ShData = Workbooks("Data.xlsx").Worksheets(2)
    
Dim sh5 As Worksheet
    Set sh5 = ThisWorkbook.Worksheets("Destnation")

Dim LR As Long 'this will work so long as no rows are hidden
    LR = ShData.Cells(Rows.Count, 1).End(xlUp).Row

Dim rng As Range 'no need to do entire rows. Probably your biggest issue.
    Set rng = ShData.Range("A2:G" & LR)

'copies Data.XLSX sheet 2 to Destination in the file WHERE this macro is being called
rng.Copy sh5.Range("A2")

'Not sure what this is doing other than trying to put a sheet name somewhere where macro is called.
Dim sh1 As Worksheet
    Set sh1 = ThisWorkbook.Worksheets("Sheet1")
    sh1.Range("H1").Value = ShData.Name
End Sub
Improve this answer

2 Comments

@ahmedemm, yea it won't be as fast as the other two that are surgical to values, but if you have considerations with formatting of date values/fonts/color/validation (which you likely don't) then this approach is worth the extra ten seconds....
PGSystemTester Thanks, I appreciate ur time & help

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.