excel - Missing Values After Using PasteSpecial in VBA

Question

I am writing a script to copy values from the Source, a CSV, to a named cell in Destination, an Excel Workbook. The script goes into Destination, clears the data starting in the named cell, then copies the data from Source and pastes it into Destination, starting at the named cell.

My named cell is called "InjP_Anchor" and sits in row 5, column 47.

This script works as I expect, for the most part. It copies the data from Source and pastes it into Destination. However, the pasted data are missing starting four rows down and 46 columns right of InjP_Anchor. In other words, it looks as if there is a deletion in the same position to InjP_Anchor as InjP_Anchor is relative to cell A1.

My code is below. Note that resultFile, shtName, and cellRef are values that I pass from a parent procedure and these string inputs don't turn up any errors.

Sub copyData(ByVal resultFile As String, ByVal shtName As String, ByVal cellRef As String)

Dim wbDest, wbSource As Workbook
Dim sht1, sht2 As Worksheet
Dim copyRange, clearRange As Range
Dim lastRow, lastCol As Integer
Dim firstRowD, firstColD, lastRowD, lastColD As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

Dim WorkingDirectory As String
Dim resultsDirectory As String
Dim namePrefix As String

WorkingDirectory = Application.ThisWorkbook.Path

resultsDirectory = "Results"

' Copy 24-hour price data
Set wbDest = Application.ThisWorkbook
Set wbSource = Workbooks.Open(Filename:=Left$(WorkingDirectory, InStrRev(WorkingDirectory, "")) & "" & resultsDirectory & "" & resultFile & ".csv")

Set sht1 = wbDest.Sheets(shtName)
Set sht2 = wbSource.Sheets(1)

Application.CutCopyMode = False

With sht1
    ' Clear destination data

    firstRowD = .Range(cellRef).Row
    firstColD = .Range(cellRef).Column
    
    lastRowD = .Cells(Rows.Count, firstColD).End(xlUp).Row
    lastColD = .Cells(firstRowD, Columns.Count).End(xlToLeft).Column
    
    Set clearRange = Range(Cells(firstRowD, firstColD), Cells(lastRowD, lastColD))
    
    Debug.Print "clearRange:"
    Debug.Print clearRange.Row, clearRange.Column
    
    clearRange.ClearContents
    
    Debug.Print "Dest range:"
    Debug.Print firstRowD, firstColD, lastRowD, lastColD

End With

With sht2
    'Copy source data
    
    lastRow = .Cells(1, 1).End(xlDown).Row
    lastCol = .Cells(1, 1).End(xlToRight).Column

    Set copyRange = Range(Cells(1, 1), Cells(lastRow, lastCol))
    
    Debug.Print "Source lastRow, lastCol"
    Debug.Print lastRow, lastCol
    
    copyRange.Copy
End With

With sht1
    ' Paste source data into destination

    .Range(cellRef).Resize(lastRow, lastCol).PasteSpecial xlPasteValues
    
    Debug.Print "Dest range"
    Debug.Print .Range(cellRef)
End With


Application.CutCopyMode = False
'wbSource.Close

ThisWorkbook.Activate
Calculate

End Sub

So far, I've discovered that omitting the code chunk clearing the old Destination data (given below) solves this problem.

With sht1
    ' Clear destination data

    firstRowD = .Range(cellRef).Row
    firstColD = .Range(cellRef).Column
    
    lastRowD = .Cells(Rows.Count, firstColD).End(xlUp).Row
    lastColD = .Cells(firstRowD, Columns.Count).End(xlToLeft).Column
    
    Set clearRange = Range(Cells(firstRowD, firstColD), Cells(lastRowD, lastColD))
    
    Debug.Print "clearRange:"
    Debug.Print clearRange.Row, clearRange.Column
    
    clearRange.ClearContents
    
    Debug.Print "Dest range:"
    Debug.Print firstRowD, firstColD, lastRowD, lastColD
End With

I have two questions:

1. Why is my value deletion code acting on the pasted Source data after the latter has been pasted?
2. How can I code a solution to first clear the table whose upper left corner is at the named cell InjP_Anchor? Sometimes the data I paste have less rows or columns than what was originally in the Destination book, so I'd like to clear out the old data before pasting the new.

Thank you!!

question from:https://stackoverflow.com/questions/65907367/missing-values-after-using-pastespecial-in-vba

Answer 1

Waitting for answers