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

S

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
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?
 
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
 
A little more digging through help files I have found the solution.

Application.DisplayAlerts = False
 
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
 
You must log in or register to reply here.
Back
Top