Results 1 to 4 of 4

Thread: Copy data from workbook(1) worksheet(1) column(2) to a different workbook.

  1. #1
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0

    Copy data from workbook(1) worksheet(1) column(2) to a different workbook.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to copy a specific range from multiple workbooks to a single file for data comparison.

    I have 10+ workbooks that need the same range in each copied to different columns in a new workbook.
    I have code that lets me browse to the folder that has my workbooks to copy from and move through each.
    My problem is the copy and paste, after it does the copy paste and then tries to close the workbook I am prompted for "there is a large amount of data in the clipboard do you want to save it for later"
    Is there a way to turn that off or automatically reply no?

  2. #2
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    This is the code i am using right now.

    Code:
    'Loop through each file in the folder
        For Each objFile In objFiles
            If InStr(1, objFile.Path, ".xls") > 0 Then
                Set wbTarget = Workbooks.Open(objFile.Path)
                With wbTarget.Worksheets(1)
                    ary(0) = objFile.Path
                End With
                
                With wbMaster.Worksheets(1)
                    lColumn = .Range("A1").End(xlToRight).Offset(0, 1).Column
                End With
                
                wbTarget.Worksheets(1).Range("I2:I255").Copy
                wbMaster.Worksheets(1).Cells(1, lColumn) = Right(ary(0), 15)
                wbMaster.Worksheets(1).Cells(2, lColumn).PasteSpecial Paste:=xlPasteAll
                
                wbTarget.Close savechanges:=False
            End If
        Next objFile

  3. #3
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    A little more digging through help files I have found the solution.

    Application.DisplayAlerts = False

  4. #4
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    Yup, that's the one! Sorry nobody saw this in time, but kudos for finding it on your own!

    What I use is this function...

    Code:
    Public Sub TOGGLEEVENTS(blnState As Boolean)
        Application.DisplayAlerts = blnState
        Application.EnableEvents = blnState
        Application.ScreenUpdating = blnState
        If blnState Then Application.CutCopyMode = False
        If blnState Then Application.StatusBar = False
    End Sub
    Then I can just pass a variable to it and all the important application properties are toggled while I run my code. So my code generally looks like this...

    Code:
    Sub SomeKindOfSubRoutineHere()
        Call TOGGLEEVENTS(False)
        '... my code goes here
        Call TOGGLEEVENTS(True)
    End Sub
    HTH
    Regards,
    Zack Barresse

Posting Permissions

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