Copy values and not links/objects or name ranges from workbooks

[aylafan]

I have a folder full of Workbooks and I have a Master Summary Workbook. The code below searches through the folder and records any rows that meet Criteria "Yes" in column K from the folder full of Workbooks (Sheet: Data) and copies the rows to the Master Summary Workbook (Sheet: CapEx).

However, I keep getting "A formula or sheet you want to move or copy contains the name "...", which already exists on the destination worksheet." I don't have any name ranges or objects in the Master Summary Workbook and I am forced to keep clicking "Yes" through all message boxes until it cycles through all the Workbooks.

So, how would I edit the code below to "only copy values" from the other Workbooks without getting this error. Thanks in advance.

Sub CopyCapEx()
Application.ScreenUpdating = False
'Display Open Dialog to select file directory
filenames = Application.GetOpenFilename("Excel Files (*.xls*)," & _
"*.xls*", 1, "Select Files", "Open", False)
 
'If the user cancels file selection then exit
If TypeName(filenames) = "Boolean" Then
Exit Sub
End If
 
'Set xls as SourceFile
SourceFile = Dir("*.xls*")
Do While SourceFile <> ""
 
If Not (SourceFile) = "Master Summary.xlsm" Then
Workbooks.Open (SourceFile)
Set XLSFile = ActiveWorkbook
With Sheets("Data")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
        If .Range("K" & i).Value = "Yes" Then
            j = j + 1
            .Range("A" & i & ":K" & i).Copy Destination:=Workbooks("Master Summary.xlsm").Sheets("CapEx").Range("A" & j)
        End If
 
 Next i
 Windows.Application.CutCopyMode = False
 XLSFile.Close False
 
 End With
 End If
 SourceFile = Dir
  Loop
Application.ScreenUpdating = True
End Sub

 

[JoeMo]

Change the copy/paste line to this:

.Range("A" & i & ":K" & i).Copy
Workbooks("Master Summary.xlsm").Sheets("CapEx").Range("A" & _
    j).PasteSpecial Paste:=xlValues

 

[aylafan]

.PasteSpecial Paste:=xlValues gives me a syntax error. Is there another solution to this?

I'm using Excel 2007 with Windows XP Professional.

 

[rlloydsr]

Hello Aylafan

I would like to know your solution I am having a similiar problem. My Macro is not as sophisticated as your but experiencing same problem.

I examine content of cells in Sheet2 for a string "Strongly Agree" and replace that with an Integer 4. I copy the Integer to Cell in Sheet1, what I get is the IF Function:

ActiveCell.FormulaR1C1 = "=IF(R[-1]C=""Test"",4,0)"

I want the Integer instead of the Function IF(A1="Strongly Agree",4,0) with an invalid reference error in Sheet1.

My Macro looks like this:

Sub Macro1()
'
' Macro1 Macro
'
'
Sheets("Sheet2").Select
'
'
Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C=""Strongly Agree"",4,0)"
Range("A3").Select
'
'
Range("A2:A2").Select
'
'
Selection.Copy
Sheets("Sheet1").Select
'
'
Sheets("Sheet1").Range("A1").Value = Sheets("Sheet1").Range("A1").Value
'
'
Range("A1").Select
ActiveSheet.PasteSpecial
Range("A1").Select
Application.CutCopyMode = False


End Sub

Any help I can get will be greatly apperciated

 

[rlloydsr]

I am also using Excel 2007

 

[JoeMo]

I am also using Excel 2007

Make the change:

Range("A1").Select
Selection.PasteSpecial paste:=xlPastevalues

 

[aylafan]

Nevermind, the code works perfectly now. The "_" dash in the Range didn't copy and pasted correctly into VBA when I tried it the first time.

 

[JoeMo]

.PasteSpecial Paste:=xlValues gives me a syntax error. Is there another solution to this?

I'm using Excel 2007 with Windows XP Professional.

Try changing to this:

.Range("A" & i & ":K" & i).Copy
With Workbooks("Master Summary.xlsm").Sheets("CapEx")
    .Range("A" & j).PasteSpecial Paste:=xlPasteValues
End With

 

[aylafan]

Thank you. This way works too. I appreciate your help.

 

[rlloydsr]

Thank you that works