Excel VBA Selection.Copy is pasting empty values in destination workbook sheet

Question

I have written an Excel VBA to copy data from selected cells from one workbook sheet to another. Here it is working fine upto certain cells, after pasting some values, after sometime VBA is pasting empty values. I mean eventhough the source cell is not empty, it is pasting empty values. I have put breakpoint and saw, but the value was there. Please help me to solve this issue.

The code is as follows.

Set objClickScriptWB = objExcelWB.Workbooks.Open(SourceWBPath)
    For intSheet = 9 To 12 'objClickScriptWB.Worksheets.Count
        If InStr(1, objClickScriptWB.Worksheets(intSheet).Name, "SC", vbTextCompare) > 0 Then
            blnScriptSheet = 1
            objClickScriptWB.Worksheets(intSheet).Activate
            For r = 24 To objClickScriptWB.Worksheets(intSheet).UsedRange.Rows.Count
                    If Trim(LCase(objClickScriptWB.Worksheets(intSheet).Cells(r, 6).Value)) <> Trim(LCase("Transaction")) And Trim(LCase(objClickScriptWB.Worksheets(intSheet).Cells(r, 6).Value)) <> Empty And objClickScriptWB.Worksheets(intSheet).Cells(r, 6).MergeArea.Cells.Count = 1 Then
  objClickScriptWB.Worksheets(intSheet).Cells(r, 6).Select



                                If blnCompSht = 0 Then
                                    Set objComparisonSheet = ThisWorkbook.Worksheets.Add
                                    objComparisonSheet.Name = "Comparison"
                                    objComparisonSheet.Activate

                                    objComparisonSheet.Cells(2, 2).Value = "Clickscript Transaction names"

                                    i = 3
                                    objExcelWB.Selection.Copy
                                    objComparisonSheet.Activate
                                    objComparisonSheet.Cells(i, 2).Select
                                    'Sheet3.Range("B2").Select
                                    'objComparisonSheet.Range("B" & i).PasteSpecial Paste:=xlPasteValues
                                    objComparisonSheet.Paste
                                    'Sheet2.Range("G2").Cells
                                    i = i + 1
                                    blnCompSht = 1
                                    'Application.Wait (Now + TimeValue("00:00:01"))
                                ElseIf blnCompSht = 1 Then
                                    ThisWorkbook.Worksheets("Comparison").Activate

                                    Dim LastRow As Integer
                                    For intRow = 2 To ThisWorkbook.Worksheets("Comparison").Rows.Count
                                        If ThisWorkbook.Worksheets("Comparison").Cells(intRow, 2).Value = Empty Then
                                            i = intRow
                                            Exit For
                                        End If

                                    Next

                                    objExcelWB.Selection.Copy
                                    ThisWorkbook.Worksheets("Comparison").Cells(i, 2).Select
                                    'ThisWorkbook.Worksheets("Comparison").Range("B" & intRow).PasteSpecial Paste:=xlPasteValues
                                    ThisWorkbook.Worksheets("Comparison").Paste
                                    i = i + 1
                                    'Application.Wait (Now + TimeValue("00:00:01"))
                                End If

                            'End If
                        'Next
                'Call CompareTxnNames(objClickScriptWB.Worksheets(intSheet).Name)
                    End If
                'Next
            Next

        End If
    Next
End Sub

Please help me

Thanks

Your code is to long and not clear the problem is. I give a suggest for you by Record Macro of what you want to copy. And then you just need to edit the code that had been auto generate by the macro as you want — Kasnady, Mar 20 '13 at 08:40
Hi the start of your Sub is missing, is there anything more missing at the start of your script? It might be a matter of taste but I usually Dim all my variables and Objects at the start of the code, that way I have a good overview of all of them. — K_B, Mar 20 '13 at 09:11
`.Value = Empty` is not a proper check for Empty cells. See http://stackoverflow.com/questions/1983649/how-do-i-express-if-value-is-not-empty-in-the-vba-language — K_B, Mar 20 '13 at 09:13

score 0 · Accepted Answer · answered Mar 20 '13 at 09:42

0

You could also directly apply the value of one cell to the other cell with a code like this:

CellTarget.Value2 = CellSource.Value2

Or in your case:

objComparisonSheet.Cells(i, 2).Value2 = objClickScriptWB.Worksheets(intSheet).Cells(r, 6).Value2

Side note:

Get into the habit of using Value2 for that is the true value of the cell compared to Value which is the value with formatting applied to it. The latter is slower and in case of date values can give wrong days and months when you arent using the US dateformat in your excel.

answered Mar 20 '13 at 09:42

K_B

3,668
1
19
29

Thanks K_B, your solution worked like gem. I was looking for this from yesterday. BTW ``Value2`` also helps us to paste only values without copying the cell format. Thanks again – harsha.cs Mar 20 '13 at 10:02
BTW I didn't understood, from earlier code, during the loop was running, the values were stopped pasting from some point of time. What may be the reason? – harsha.cs Mar 20 '13 at 10:05

Excel VBA Selection.Copy is pasting empty values in destination workbook sheet

1 Answers1