Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 27

Thread: Copy/Paste Between Workbooks faster..?

  1. #1
    Board Regular
    Join Date
    Nov 2015
    Posts
    183
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Copy/Paste Between Workbooks faster..?

    Hey guys,

    Need some desperate help with this one the code works fine but it's just sooooo **** slow... "probably my fault"

    Any advise? Needs to be a hell of a lot quicker for what I'm trying to achieve.

    Sub ImportBlueDownpipe()


    LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row


    For i = 1 To LastRow


    Sheets("Downpipe").Select


    If Range("H" & i).Value = "MB" And Range("W" & i).Value = "Downpipe" Then
    Rows(i).Select
    Selection.Copy


    Workbooks.Open Filename:="B:\Best Shed Scheduler.xlsm" '''''Found on local machine drive'''''nothing networked''''


    Dim p As Integer, q As Integer


    p = Worksheets.Count


    For q = 1 To p


    Next q


    Sheets("Downpipe Machine").Select


    erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


    ActiveSheet.Cells(erow, 1).Select
    ActiveSheet.Paste
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Application.CutCopyMode = False




    End If

    Next i


    End Sub
    Last edited by Lukums; Mar 30th, 2017 at 07:11 PM.

  2. #2
    Board Regular HotRhodium's Avatar
    Join Date
    Nov 2015
    Posts
    151
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy/Paste Between Workbooks faster..?

    Yeah you have a lot to gain by not selecting and activating everything. Could you check the code below for intent?

    Code:
    Sub ImportBlueDownpipe()
    Dim DownpipeWS As Worksheet, DownpipeMachineWS As Worksheet
     Set DownpipeWS = Sheets("Downpipe")
     LastRow = DownpipeWS.Range("A" & DownpipeWS.Rows.Count).End(xlUp).Row
    
      For i = 1 To LastRow
     
       If DownpipeWS.Range("H" & i).Value = "MB" And DownpipeWS.Range("W" & i).Value = "Downpipe" Then
       DownpipeWS.Rows(i).Copy
      
       Workbooks.Open Filename:="B:\Best Shed Scheduler.xlsm" '''''Found on local machine drive'''''nothing networked''''
      
       Dim p As Integer, q As Integer
      
       p = Worksheets.Count
      
    '    For q = 1 To p ''I have no idea what this does is for something later?
    '
    '    Next q
      
       Set DownpipeMachineWS = Sheets("Downpipe Machine")
      
       erow = DownpipeMachineWS.Cells(DownpipeMachineWS.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
      
       DownpipeMachineWS.Cells(erow, 1).Paste
       ActiveWorkbook.Save
       ActiveWorkbook.Close
       Application.CutCopyMode = False
      
       End If
     
      Next i
    
     End Sub

  3. #3
    Board Regular
    Join Date
    Nov 2015
    Posts
    183
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy/Paste Between Workbooks faster..?

    Quote Originally Posted by HotRhodium View Post
    Yeah you have a lot to gain by not selecting and activating everything. Could you check the code below for intent?

    Code:
    Sub ImportBlueDownpipe()
    Dim DownpipeWS As Worksheet, DownpipeMachineWS As Worksheet
     Set DownpipeWS = Sheets("Downpipe")
     LastRow = DownpipeWS.Range("A" & DownpipeWS.Rows.Count).End(xlUp).Row
    
      For i = 1 To LastRow
     
       If DownpipeWS.Range("H" & i).Value = "MB" And DownpipeWS.Range("W" & i).Value = "Downpipe" Then
       DownpipeWS.Rows(i).Copy
      
       Workbooks.Open Filename:="B:\Best Shed Scheduler.xlsm" '''''Found on local machine drive'''''nothing networked''''
      
       Dim p As Integer, q As Integer
      
       p = Worksheets.Count
      
    '    For q = 1 To p ''I have no idea what this does is for something later?
    '
    '    Next q
      
       Set DownpipeMachineWS = Sheets("Downpipe Machine")
      
       erow = DownpipeMachineWS.Cells(DownpipeMachineWS.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
      
       DownpipeMachineWS.Cells(erow, 1).Paste
       ActiveWorkbook.Save
       ActiveWorkbook.Close
       Application.CutCopyMode = False
      
       End If
     
      Next i
    
     End Sub
    I see what you've done there, and yes that code is for something later on but I'll try execute what you've suggest and report back!

  4. #4
    Board Regular
    Join Date
    Nov 2015
    Posts
    183
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy/Paste Between Workbooks faster..?

    Hey mate, no good bombs out on : DownpipeMachineWS.Cells(erow, 1).Paste

    Method of object can't be used.



  5. #5
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    16,635
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Copy/Paste Between Workbooks faster..?

    dont have Excel at the moment .....but consider using auto filter to get the visible cells in the downpipe sheet then copy across in one block..
    This is UNTESTED

    Code:
    Sub ImportBlueDownpipe()
    Dim DownpipeWS As Worksheet, DownpipeMachineWS As Worksheet
     Set DownpipeWS = Sheets("Downpipe")
     With ActiveSheet
                .AutoFilterMode = False
                .UsedRange.AutoFilter
                .UsedRange.AutoFilter field:=8, Criteria1:="MB"
                .UsedRange.AutoFilter field:=23, Criteria1:="Downpipe"
                .SpecialCells(xlCellTypeVisible).Copy
                .AutoFilter
        End With
      
       Workbooks.Open Filename:="B:\Best Shed Scheduler.xlsm" '''''Found on local machine drive'''''nothing networked''''
      
       Set DownpipeMachineWS = Sheets("Downpipe Machine")
      
       erow = DownpipeMachineWS.Cells(DownpipeMachineWS.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
      
       DownpipeMachineWS.Cells(erow, 1).Paste
       ActiveWorkbook.Save
       ActiveWorkbook.Close
       Application.CutCopyMode = False
      
       End If
    
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  6. #6
    Board Regular
    Join Date
    Nov 2015
    Posts
    183
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy/Paste Between Workbooks faster..?

    Quote Originally Posted by Michael M View Post
    dont have Excel at the moment .....but consider using auto filter to get the visible cells in the downpipe sheet then copy across in one block..
    This is UNTESTED

    Code:
    Sub ImportBlueDownpipe()
    Dim DownpipeWS As Worksheet, DownpipeMachineWS As Worksheet
     Set DownpipeWS = Sheets("Downpipe")
     With ActiveSheet
                .AutoFilterMode = False
                .UsedRange.AutoFilter
                .UsedRange.AutoFilter field:=8, Criteria1:="MB"
                .UsedRange.AutoFilter field:=23, Criteria1:="Downpipe"
                .SpecialCells(xlCellTypeVisible).Copy
                .AutoFilter
        End With
      
       Workbooks.Open Filename:="B:\Best Shed Scheduler.xlsm" '''''Found on local machine drive'''''nothing networked''''
      
       Set DownpipeMachineWS = Sheets("Downpipe Machine")
      
       erow = DownpipeMachineWS.Cells(DownpipeMachineWS.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
      
       DownpipeMachineWS.Cells(erow, 1).Paste
       ActiveWorkbook.Save
       ActiveWorkbook.Close
       Application.CutCopyMode = False
      
       End If
    
    End Sub
    I didn't know such things were possible... I'll look into it. THANKS

    didn't

  7. #7
    Board Regular
    Join Date
    Nov 2015
    Posts
    183
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy/Paste Between Workbooks faster..?

    Hello Michael M

    That was bloody amazing to see it's exactly what I'm looking for!!!

    However, it bombs at .SpecialCells(x1CellTypeVisible).Copy

    Object osnt support this property or method RUn time error 438

    Any ideas?

  8. #8
    Board Regular HotRhodium's Avatar
    Join Date
    Nov 2015
    Posts
    151
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy/Paste Between Workbooks faster..?

    Ok I see this should paste. What data exactly are you wanting to paste? It will move much faster if you paste values only fyi.

    Code:
    Sub ImportBlueDownpipe()
    Dim DownpipeWS As Worksheet, DownpipeMachineWS As Worksheet
    
     LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
      Set DownpipeWS = Sheets("Downpipe")
    
      For i = 1 To LastRow
     
       If DownpipeWS.Range("H" & i).Value = "MB" And DownpipeWS.Range("W" & i).Value = "Downpipe" Then
       DownpipeWS.Rows(i).Copy
      
       Workbooks.Open Filename:="B:\Best Shed Scheduler.xlsm" '''''Found on local machine drive'''''nothing networked''''
      
       Dim p As Integer, q As Integer
      
       p = Worksheets.Count
      
    '    For q = 1 To p ''I have no idea what this does is for something later?
    '
    '    Next q
      
       Set DownpipeMachineWS = Sheets("Downpipe Machine")
      
       erow = DownpipeMachineWS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
      
       DownpipeMachineWS.Cells(erow, 1).PasteSpecial xlPasteAll
       ActiveWorkbook.Save
       ActiveWorkbook.Close
       Application.CutCopyMode = False
      
       End If
     
      Next i
    
     End Sub

  9. #9
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    16,635
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Copy/Paste Between Workbooks faster..?

    ....missed a line

    Code:
    Sub ImportBlueDownpipe()
    Dim DownpipeWS As Worksheet, DownpipeMachineWS As Worksheet
     Set DownpipeWS = Sheets("Downpipe")
     With ActiveSheet
                .AutoFilterMode = False
                .UsedRange.AutoFilter
                .UsedRange.AutoFilter field:=8, Criteria1:="MB"
                .UsedRange.AutoFilter field:=23, Criteria1:="Downpipe"
                .UsedRange.SpecialCells(xlCellTypeVisible).Copy
                .AutoFilter
        End With
      
       Workbooks.Open Filename:="B:\Best Shed Scheduler.xlsm" '''''Found on local machine drive'''''nothing networked''''
      
       Set DownpipeMachineWS = Sheets("Downpipe Machine")
      
       erow = DownpipeMachineWS.Cells(DownpipeMachineWS.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
      
       DownpipeMachineWS.Cells(erow, 1).Paste
       ActiveWorkbook.Save
       ActiveWorkbook.Close
       Application.CutCopyMode = False
      
       
    
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  10. #10
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    16,635
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Copy/Paste Between Workbooks faster..?

    A bit cleaner....but still UNTESTED

    Code:
    Sub ImportBlueDownpipe()
    Dim DownpipeMachineWS As Worksheet
     With Sheets("Downpipe").UsedRange
                .AutoFilter
                .AutoFilter field:=8, Criteria1:="MB"
                .AutoFilter field:=23, Criteria1:="Downpipe"
                .SpecialCells(xlCellTypeVisible).Copy
                .AutoFilter
        End With
       Workbooks.Open Filename:="B:\Best Shed Scheduler.xlsm" '''''Found on local machine drive'''''nothing networked''''
       Set DownpipeMachineWS = Sheets("Downpipe Machine")
       erow = DownpipeMachineWS.Cells(DownpipeMachineWS.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
       DownpipeMachineWS.Cells(erow, 1).Paste
       ActiveWorkbook.Save
       ActiveWorkbook.Close
       Application.CutCopyMode = False
    End Sub
    Last edited by Michael M; Mar 30th, 2017 at 09:26 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •