Copy from one workbook to another (just format and values)

[gheyman]

I have this code below which is just pasting in the values (it works as expected). But under the section for Coverpage, when that is pasted in I want to Paste in both the format and values. But I dont want the formulas that are in the source so I cannot do "Paste"

(' Copy & Paste-Value: Coverpage Tab)

Any help is appreciated!

Sub Get_Data()

'Retriev_BOETab Macro
'
'
    ' Retrieve Current Workbook Name
    Dim Mtrl_Resource As Workbook
    Set Mtrl_Resource = ThisWorkbook
    
    Sheets("Tracker").Activate

    Application.ScreenUpdating = False
    
    ' Open Dialog Box to Select File to Copy From & Save it's Name in a Variable
    MsgBox "Retrieve the 'Material Estimates' tab from the Material Cost Estimates File - Select the MCE File."
    Dim filePicker As Office.FileDialog
    Set filePicker = Application.FileDialog(msoFileDialogFilePicker)
    With filePicker
        .Filters.Clear
        .Title = "Select an Excel File"
        .AllowMultiSelect = False
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1

        
        Dim selectedFile As String
        
        If .Show = True Then
            selectedFile = .SelectedItems(1)
        End If
    End With
    
    ' Open Selected File & Copy Data
    If selectedFile <> "" Then
        Dim MaterialEstBook As Workbook
        Dim sht As Worksheet, Flag As Boolean
        Set MaterialEstBook = Workbooks.Open(selectedFile, ReadOnly:=True)
        
        Workbooks(Mtrl_Resource.Name).Worksheets("Tracker").Range("C6").Value = selectedFile
    
'***************************************************************************************************
        ' Copy & Paste-Value: Coverpage Tab
        
            'Unhide
            Workbooks(Mtrl_Resource.Name).Sheets("Coverpage").Visible = True
            
            'Check for tabs existence
            
            For Each sht In MaterialEstBook.Worksheets
            If LCase(sht.Name) = LCase("Coverpage") Then
            
            Workbooks(MaterialEstBook.Name).Worksheets("Coverpage").Range("A1:AC50000").Copy
            
            
            '################
            'Here is where I want it to paste it in as values but I also need the formatting from the source.  I cannot do "Paste" because the source has formulas which I don't want.
            'Thanks for the help
            '################

            Workbooks(Mtrl_Resource.Name).Worksheets("Coverpage").Range("A1").PasteSpecial _
                Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Workbooks(Mtrl_Resource.Name).Worksheets("Tracker").Range("C7").Value = Now()
                
            Flag = True
            Exit For
            End If
            Next sht
            If Not Flag Then
            Workbooks(Mtrl_Resource.Name).Worksheets("Tracker").Range("C7").Value = "Tab not found"
            End If
            Application.CutCopyMode = False
'***************************************************************************************************
        
'***************************************************************************************************
        ' Copy & Paste-Value: Task Input Tab
        
            'Unhide
            Workbooks(Mtrl_Resource.Name).Sheets("Tasks_Input").Visible = True
            
            'Check for tabs existence
            
            For Each sht In MaterialEstBook.Worksheets
            If LCase(sht.Name) = LCase("Tasks Input") Then
            
            Workbooks(MaterialEstBook.Name).Worksheets("Tasks Input").Range("A1:AC50000").Copy
            Workbooks(Mtrl_Resource.Name).Worksheets("Tasks_Input").Range("A1").PasteSpecial _
                Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Workbooks(Mtrl_Resource.Name).Worksheets("Tracker").Range("C8").Value = Now()
                
            Flag = True
            Exit For
            End If
            Next sht
            If Not Flag Then
            Workbooks(Mtrl_Resource.Name).Worksheets("Tracker").Range("C8").Value = "Tab not found"
            End If
            Application.CutCopyMode = False
'***************************************************************************************************

'***************************************************************************************************
        ' Copy & Paste-Value: Materials Input Tab
        
            'Unhide
            Workbooks(Mtrl_Resource.Name).Sheets("Materials_Input").Visible = True
            
            'Check for tabs existence
            
            For Each sht In MaterialEstBook.Worksheets
            If LCase(sht.Name) = LCase("Materials Input") Then
            
            Workbooks(MaterialEstBook.Name).Worksheets("Materials Input").Range("A1:AC50000").Copy
            Workbooks(Mtrl_Resource.Name).Worksheets("Materials_Input").Range("A1").PasteSpecial _
                Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Workbooks(Mtrl_Resource.Name).Worksheets("Tracker").Range("C9").Value = Now()
                
            Flag = True
            Exit For
            End If
            Next sht
            If Not Flag Then
            Workbooks(Mtrl_Resource.Name).Worksheets("Tracker").Range("C9").Value = "Tab not found"
            End If
            Application.CutCopyMode = False
'***************************************************************************************************

'***************************************************************************************************
        ' Copy & Paste-Value: Material_Distribution Tab
        
            'Unhide
            Workbooks(Mtrl_Resource.Name).Sheets("Material_Distribution").Visible = True
            
            'Check for tabs existence
            
            For Each sht In MaterialEstBook.Worksheets
            If LCase(sht.Name) = LCase("Material Distribution") Then
            
            Workbooks(MaterialEstBook.Name).Worksheets("Material Distribution").Range("A1:AC50000").Copy
            Workbooks(Mtrl_Resource.Name).Worksheets("Material_Distribution").Range("A1").PasteSpecial _
                Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Workbooks(Mtrl_Resource.Name).Worksheets("Tracker").Range("C10").Value = Now()
                
            Flag = True
            Exit For
            End If
            Next sht
            If Not Flag Then
            Workbooks(Mtrl_Resource.Name).Worksheets("Tracker").Range("C10").Value = "Tab not found"
            End If
            Application.CutCopyMode = False
'***************************************************************************************************

'***************************************************************************************************
        ' Copy & Paste-Value: Material_Assoc_Costs Tab
        
            'Unhide
            Workbooks(Mtrl_Resource.Name).Sheets("Material_Assoc_Costs").Visible = True
            
            'Check for tabs existence
            
            For Each sht In MaterialEstBook.Worksheets
            If LCase(sht.Name) = LCase("Material Assoc Costs") Then
            
            Workbooks(MaterialEstBook.Name).Worksheets("Material Assoc Costs").Range("A1:AC50000").Copy
            Workbooks(Mtrl_Resource.Name).Worksheets("Material_Assoc_Costs").Range("A1").PasteSpecial _
                Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Workbooks(Mtrl_Resource.Name).Worksheets("Tracker").Range("C11").Value = Now()
                
            Flag = True
            Exit For
            End If
            Next sht
            If Not Flag Then
            Workbooks(Mtrl_Resource.Name).Worksheets("Tracker").Range("C11").Value = "Tab not found"
            End If
            Application.CutCopyMode = False
'***************************************************************************************************

'***************************************************************************************************
        ' Copy & Paste-Value: Material_Ad_Hoc Tab
        
            'Unhide
            Workbooks(Mtrl_Resource.Name).Sheets("Material_Ad_Hoc").Visible = True
            
            'Check for tabs existence
            
            For Each sht In MaterialEstBook.Worksheets
            If LCase(sht.Name) = LCase("Material Ad Hoc") Then
            
            Workbooks(MaterialEstBook.Name).Worksheets("Material Ad Hoc").Range("A1:AC50000").Copy
            Workbooks(Mtrl_Resource.Name).Worksheets("Material_Ad_Hoc").Range("A1").PasteSpecial _
                Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Workbooks(Mtrl_Resource.Name).Worksheets("Tracker").Range("C12").Value = Now()
                
            Flag = True
            Exit For
            End If
            Next sht
            If Not Flag Then
            Workbooks(Mtrl_Resource.Name).Worksheets("Tracker").Range("C12").Value = "Tab not found"
            End If
            Application.CutCopyMode = False
'***************************************************************************************************
                        
    
            ' Clear Clipboard
            Application.CutCopyMode = False
        
        
        
    
        ' Close Extra Workbook
        MaterialEstBook.Close SaveChanges:=False
        Set MaterialEstBook = Nothing
    End If
    
    Sheets("Tracker").Select

End Sub

Thank you!

 

[JGordon11]

just repeat the pastevalues line but change xlPasteValues to xlPasteFormats

 

[gheyman]

Thank you