i need to create loop to copy from excel to paste in Notepad

Question

I have data in two columns A(Barcode) & B(count). I need a macro that copies data from Column A and paste it in notepad multiple times as mentioned in column B. Below is my code that works well for only selected row. Please help to create a loop so the macro works till last row containing data: Sample Data

Sub Receivinggg()
    Dim obj As New DataObject
    Dim bc As Variant
    Dim i As Integer
    Dim j As Integer
    j = Cells(ActiveCell.Row, 2).Value
    For i = 1 To j
        bc = Selection.Value
            If Len(bc) < 11 Then
                bc = "0" & Selection.Value
            End If
        obj.SetText bc
        obj.PutInClipboard
        VBA.AppActivate ("1 - Notepad"), 0
        SendKeys "+{INSERT}"
        Application.Wait Now + TimeValue("00:00:02")
        SendKeys "{Enter}"
        Next i
        VBA.AppActivate ("Receiving - Excel"), 0
    SendKeys "{Down}"
End Sub

Why do you want to paste it into Notepad - wouldn't it be much easier to write it into a text file and then open that file with Notepad? — FunThomas, Mar 01 '21 at 16:03
I actually need to input the data in a web form and submit there. I have been practicing it with notepad. — Muhammad Ahmed, Mar 01 '21 at 16:08
So your question has nothing to do with Notepad nor with Cut&Paste? You should reword your question - at least for me it's unclear what your problem is. — FunThomas, Mar 01 '21 at 16:18
copy and paste is the basic requirement. it is working fine for one selected cell. issue is that i want it to be executed for all cells in column A that contain data — Muhammad Ahmed, Mar 01 '21 at 16:27
So your problem is *How do I loop over all cells of a column"*? Or *"How do I find the last cell used in a range"*. Lots of answers about that here on SO, eg https://stackoverflow.com/a/11169920/7599798. Don't include all the other stuff into the question (and especially not in the title). — FunThomas, Mar 01 '21 at 16:34

score 0 · Accepted Answer · answered Mar 05 '21 at 14:35

Issue stands resolved, I succeeded to add a loop within loop to get the required results. thanks anyways.

    Sub test()
    Dim bc As Variant
    Dim cellrow As Integer
    Dim cellcol As Integer
    Dim lastrow As Integer
    Dim x As Integer
    Dim i As Integer
    Dim obj As New DataObject
    Dim j As Integer
    cellrow = 2
    cellcol = 1
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastrow
    Cells(cellrow, cellcol).Select
        j = Cells(ActiveCell.Row, 2).Value
            For x = 1 To j
                    bc = Selection.Value
                    If Len(bc) < 11 Then
                    bc = "0" & Selection.Value
                    End If
                    obj.SetText bc
                    obj.PutInClipboard
                    VBA.AppActivate ("1 - Notepad"), 0
               ' VBA.AppActivate ("aaaa - WebUtil"), 0
                    SendKeys "+{INSERT}"
                    Application.Wait Now + TimeValue("00:00:03")
                    SendKeys "{Enter}"
                Next x
    VBA.AppActivate ("Receiving - Excel"), 0
    cellrow = cellrow + 1
    Next i
End Sub

i need to create loop to copy from excel to paste in Notepad

1 Answers1