0

I've got this code but it pastes the cell formatting from the original document into the master file, how can I remove the formatting from the output please?

Option Explicit

Sub CopyPastefiles()

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim MyFolder As String
    Dim StartSht As Worksheet, ws As Worksheet
    Dim WB As Workbook
    Dim i As Integer

    'turn screen updating off - makes program faster
    'Application.ScreenUpdating = False

    'location of the folder in which the desired TDS files are
    MyFolder = "U:\Documents\DeleteMe\Sycle\"

    Set StartSht = ActiveSheet
    Set StartSht = Workbooks("masterfile.xlsx").Sheets("Sheet1")

    'create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'get the folder object
    Set objFolder = objFSO.GetFolder(MyFolder)
    i = 1

    'loop through directory file and print names
    For Each objFile In objFolder.Files
        If LCase(Right(objFile.Name, 3)) = "xls" Or LCase(Left(Right(objFile.Name, 4), 3)) = "xls" Then
            'print file name to Column 1
            Workbooks.Open Filename:=MyFolder & objFile.Name
            Set WB = ActiveWorkbook
            'print TOOLING DATA SHEET(TDS): values to Column 2
            With WB
                For Each ws In .Worksheets
                    StartSht.Cells(i + 1, 10) = objFile.Name
                    With ws
                        .Range("e6").Copy StartSht.Cells(i + 1, 4)
                        .Range("e7").Copy StartSht.Cells(i + 1, 5)
                        .Range("e8").Copy StartSht.Cells(i + 1, 6)
                        
                    End With
                    
                    i = i + 1
                'move to next file
                Next ws
                'close, do not save any changes to the opened files
                .Close SaveChanges:=False
            End With
        End If
    'move to next file
    Next objFile
    
    'turn screen updating back on
    'Application.ScreenUpdating = True

End Sub

thanks for you help.

1
  • If you want just to copy the values, use .StartSht.Cells(i + 1, 4) = .Range("e6") Commented Oct 22, 2020 at 7:51

1 Answer 1

1

Instead of using .Copy to directly paste the values into the destination, you can use .PasteSpecial Paste:=xlPasteValues.

I.e. something like

.Range("e6").Copy
StartSht.Cells(i + 1, 4).PasteSpecial Paste:=xlPasteValues

for your first line.

Or you can just set the cell equal to the range you're copying, as suggested in the comments on your question.

.StartSht.Cells(i + 1, 4) = .Range("E6")
Sign up to request clarification or add additional context in comments.

1 Comment

thank you for your assistance @eirikdaude. that was correct

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.