CODEWITHSUNDEEP
excelvba
jayesh
jayesh

Reputation: 377

Why this VBA code to copy range fails intermittently?

I wrote below procedure to copy range of cells from an input sheet to output sheet. Output sheet is part of master template. After the copy, I call SaveAs function on master workbook to save the master workbook on file system with another name. 

Private Sub copyRange(inputRange As Range, outputRange As Range)

inputRange.NumberFormat = "0.00000000"
inputRange.Copy
outputRange.PasteSpecial xlPasteValuesAndNumberFormats

End Sub

I'm calling this function multiple times from another procedure for each range that i want to copy. Calling code looks like this:

Dim InputSheet As Worksheet
Dim OutputSheet As Worksheet

Set InputSheet = Application.Workbooks.Open("C:/inputFile.xlsx", False, vbReadOnly).Worksheets("Sheet1")
Set OutputSheet = OutputSheet 'this is a sheet of master template

copyRange InputSheet.Range("E15:G28"), OutputSheet.Range("B12:D25")
copyRange InputSheet.Range("E33:G37"), OutputSheet.Range("B30:D34")

copyRange InputSheet.Range("I15:K28"), OutputSheet.Range("E12:G25")
copyRange InputSheet.Range("I33:K37"), OutputSheet.Range("E30:G34")

copyRange InputSheet.Range("M15:O28"), OutputSheet.Range("H12:J24")
copyRange InputSheet.Range("M33:O37"), OutputSheet.Range("H30:J34")

Above code keeps failing around 1 out of 5 times with error "paste special method of range class failed". Most of the time it runs fine and output file is getting produced as expected. Is there any way to prevent this error?

Upvotes: 0

Views: 114

Answers (1)

jayesh
jayesh

Reputation: 377

After trying many things, finally I've removed call to pastespecial method. 

Private Sub copyRange(inputRange As Range, outputRange As Range)

inputRange.NumberFormat = "0.00000000"
inputRange.value2 = outputRange.value2

End Sub

This approach have resolve intermittent issues while copying the range.

Upvotes: 1

Related Questions