VBA infuriating problem (for me) - PasteAll not quite working

[stoothom]

Hi,

      SourceRange.Copy
      DestRange.PasteSpecial Paste:=xlPasteAll

The above code as part of a wider range code Copies data from a selection of workbooks and pastes them into a new workbook one after the other.

The source workbooks contain cells with mixed formatting and formula.

Unfortunately the source sheets often contain formula quoted by $ sign. This means when I use the PasteAll above it works for everything except for where there is constrained formula.

is there a way to set up a paste such I paste in all (thus keeping the cell mixed formula) but ONLY ignore formula.

Many thanks.

 

[Norie]

What happens if you do a direct copy instead of copy/paste?

SourceRange.Copy Destination:=DestRange.PasteSpecial

 

[stoothom]

What happens if you do a direct copy instead of copy/paste?

SourceRange.Copy Destination:=DestRange.PasteSpecial

Hi Norie, many thanks for your reply. That also unfortunately doesn't seem to work. Full code here for reference:

Sub MergeAllWorkbooks2()
Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim NRow As Long
    Dim FileName As String
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range
    
    ' Creates a new workbook
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    
    ' Modifies the path
    FolderPath = "C:\OfflineStorage\CRCs\"
    
    ' NRow keeps track of where to insert new rows in the destination workbook
    NRow = 1
    
    ' Call Dir the first time, pointing it to all Excel files in the folder path
    FileName = Dir(FolderPath & "*.xl*")
    
    ' Loop until Dir returns an empty string
    Do While FileName <> ""
        ' Open a workbook in the folder
        Set WorkBk = Workbooks.Open(FolderPath & FileName)
        
        ' Set the cell in column A to be the file name
        SummarySheet.Range("A" & NRow).Value = FileName
        
        ' Modify the range
        ' It can span multiple rows.
       Dim LastRow As Long
    LastRow = WorkBk.Worksheets("CRC").Cells.Find(What:="*", _
                 After:=WorkBk.Worksheets("CRC").Cells.Range("A1"), _
                 SearchDirection:=xlPrevious, _
                 LookIn:=xlFormulas, _
                 SearchOrder:=xlByRows).Row
    Set SourceRange = WorkBk.Worksheets("CRC").Range("A1:K" & LastRow)
        
        ' Set the destination range to start at column B and
        ' be the same size as the source range.
        Set DestRange = SummarySheet.Range("A" & NRow)
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
           SourceRange.Columns.Count)
           
        ' Copy over the values from the source to the destination.
      SourceRange.Copy
      DestRange.PasteSpecial Paste:=xlPasteAll
        ' Increase NRow so that we know where to copy data next.
        NRow = NRow + DestRange.Rows.Count
        
        ' Close the source workbook without saving changes.
        WorkBk.Close savechanges:=False
        
        ' Use Dir to get the next file name.
        FileName = Dir()
    Loop
    
    ' Call AutoFit on the destination sheet so that all
    ' data is readable.
    Columns("A").ColumnWidth = 11.57
    Columns("B").ColumnWidth = 68.14
    Columns("C").ColumnWidth = 13.71
    Columns("D").ColumnWidth = 19.14
    Columns("E").ColumnWidth = 57
    Columns("F").ColumnWidth = 14.71
    Columns("G").ColumnWidth = 13.71
    Columns("H").ColumnWidth = 40
    Columns("I").ColumnWidth = 25.57
    Columns("J").ColumnWidth = 29.14
    Columns("K").ColumnWidth = 45
    ActiveCell.Cells.Select
    ActiveCell.Cells.EntireRow.AutoFit
    
End Sub

 

[Norie]

What exactly is the problem?

Are the references in the formulas being mucked up?

 

[stoothom]

the Problem is:

If I've two sheets (actually I've about 20) I'm pulling from it will place one after the other into a new sheet (such will enable me to summarise easier)
In both sheets (as an example) I may have a formula which references $d1$. Unfortunately because the above code also pulls the formula any sheet (other than the first) will be in wrong as it will also reference $d1$ whereas once the second sheet is pasted in I actually would like it looking at d20 *note it won't always be equal increments.

I can't just pull the values in because I also need be concerned with full formatting i.e. some cells have black and red text in..

Does this make sense? sorry if it doesn't.