How do I insert cell Values instead of cell formulas?-CodePudding

Everything is working, except for the insert function. I can't figure out how to get it to insert the values from the "pull" file to the "put" file.

I'm getting the standard #REF in each "put" file cell when all I want is the basic text from the "pull" file (Not the formulas).

    Sub PullTally()
 pullFile = Application.GetOpenFilename(fileFilter:="All Files (* . *) , * . * ")  'Copy From'
 putFile = Application.GetOpenFilename(fileFilter:="All Files (* . *) , * . * ")   'Insert To'

Workbooks.Open fileName:=pullFile
Worksheets("Sheet1").Range("K4:M19").Copy

Workbooks.Open fileName:=putFile
ActiveSheet.Range("A100").Insert xlShiftDown

    MsgBox Pulled
End Sub

I have tried .Value but that obviously didn't work and I'm struggling to find anything helpful.

CodePudding user response：

If you really want to insert (not just paste) cells so the existing data gets shifted town you first need to insert empty cells and then .PasteSpecial the values only.

Option Explicit 

Public Sub PullTally()
    ' get source and destination workbook files
    Dim pullFile As String
    pullFile = Application.GetOpenFilename(fileFilter:="All Files (* . *) , * . * ")  'Copy From'
    Dim putFile As String
    putFile = Application.GetOpenFilename(fileFilter:="All Files (* . *) , * . * ")   'Insert To'

    'open source workbook
    Dim SourceWb As Workbook
    Set SourceWb = Workbooks.Open(FileName:=pullFile)
    
    ' define range to copy from that source file
    Dim CopyRange As Range
    Set CopyRange = SourceWb.Worksheets("Sheet1").Range("K4:M19")

    ' open destination workbook
    Dim DestWb As Workbook
    Set DestWb = Workbooks.Open(FileName:=putFile)

    ' get a range of the size of the source range and …
    With DestWb.Worksheets(1).Range("A100").Resize(CopyRange.Rows.Count, CopyRange.Columns.Count)
        ' … insert empty cells
        .Insert xlShiftDown
        ' … copy source
        CopyRange.Copy
        ' … paste values into those empty cells
        .PasteSpecial Paste:=xlPasteValues
    End With

    MsgBox "Pulled"
End Sub

CodePudding user response：

Try this.

Sub PullTally()

    pullFile = Application.GetOpenFilename(fileFilter:="All Files (* . *) , * . * ")  'Copy From'
    putFile = Application.GetOpenFilename(fileFilter:="All Files (* . *) , * . * ")   'Insert To'
    
    Workbooks.Open Filename:=pullFile
    Worksheets("Sheet1").Range("K4:M19").Copy
    
    Workbooks.Open Filename:=putFile
    ActiveSheet.Range("A100").PasteSpecial Paste:=xlPasteValues
    
    MsgBox Pulled
    
End Sub