Populating Column "A" after copy & paste

[Anthony86]

Hi Again,

Can anyone help me with copying/pasting using a userform.

I have a master spreadsheet that I'm copying data from an pasting to a different workbook. I've got this bit working fine but once the pasting over has happened, I need to populate column "A" from a textbox value all the way down until the last used cell in column "B"

This is what I'm using to copy and paste data across.

With wsSource.Range("A2:D" & wsSource.Range("A" & Rows.Count).End(xlUp).Row)        .AutoFilter Field:=1, Criteria1:=txtFileNumber.Value
        
        .Offset(1).Columns(1).SpecialCells(xlVisible).Copy
        Range("A" & Rows.Count).End(xlUp).Offset(1, 1).PasteSpecial xlPasteValues
        .Offset(1).Columns(4).SpecialCells(xlVisible).Copy
        Range("A" & Rows.Count).End(xlUp).Offset(1, 2).PasteSpecial xlPasteValues
        .Offset(1).Columns(5).SpecialCells(xlVisible).Copy
        Range("A" & Rows.Count).End(xlUp).Offset(1, 3).PasteSpecial xlPasteValues
        Range("A" & Rows.Count).End(xlUp).Offset(1) = txtBoxNumber.Value

 

[Anthony86]

That worked! Its working great now.

IS there anyway of adding a counter to it? So if column "A" is the same 'Location' then count everything that is pasted to that 'location'

 

[Fluff]

Maybe

Sub chk()
   Dim wsSource As Worksheet
   Dim wsTarget As Worksheet
   Dim nextRow As Long
   Dim NxtRw As Long
   Dim EndRw As Long
   Dim Cnt As Long
   

   Workbooks.Open (ThisWorkbook.Path & "\*****.xls")



   'worksheets and workbooks
   Set wsSource = Workbooks("******.xlsm").Sheets("Control")
   Set wsTarget = Workbooks("***.xls").Sheets("Log")


   'filter and copy
   NxtRw = wsTarget.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
   Cnt = WorksheetFunction.CountIf(wsSource.Range("A:A"), txtFileNumber.Value)
   If Cnt = 0 Then
      wsTarget.Range("A" & NxtRw).Value = txtBoxNumber.Value
      wsTarget.Range("B" & NxtRw).Value = txtFileNumber.Value
   Else
      With wsSource.Range("A1:D" & wsSource.Range("A" & Rows.Count).End(xlUp).Row)
         .AutoFilter Field:=1, Criteria1:=txtFileNumber.Value
         .Offset(1).Columns(1).SpecialCells(xlVisible).Copy
         wsTarget.Range("B" & NxtRw).PasteSpecial xlPasteValues
         .Offset(1).Columns(3).Resize(, 2).SpecialCells(xlVisible).Copy
         wsTarget.Range("C" & NxtRw).PasteSpecial xlPasteValues
         EndRw = wsTarget.Range("B" & Rows.Count).End(xlUp)
         wsTarget.Range("A" & NxtRw & ":A" & EndRw) = txtBoxNumber.Value
      End With
   End If


   wsSource.AutoFilterMode = False
   txtFileNumber.Value = Null
   txtFileNumber.SetFocus
   MsgBox EndRw - NxtRw
End Sub

 

[Anthony86]

No that counted every row.

I've got a counter on another macro that I've used in the past, just don't know how to use it with this one.

Function CountBoxesInALocation(ByVal pLocation As String) As Integer    Dim NumRows As Integer
    Dim bFound As Boolean
    Dim NumberOfBoxes As Integer
    NumberOfBoxes = 0
    NumRows = ActiveSheet.UsedRange.Rows.Count
    bFound = False
    For i = 1 To NumRows
        If Cells(i, 2) = UCase(pLocation) Then
            If UCase(Left(Cells(i, 1).Value, 1)) = "D" Then
                NumberOfBoxes = NumberOfBoxes + 1
            End If
        End If
    Next i
    CountBoxesInALocation = NumberOfBoxes
End Function

iCounter = CountBoxesInALocation(Me.txtLocation.Value)

Can't seem to get this to work with the code I'm using now.

 

[Fluff]

The code I suggested counts the number of rows pasted to the target book, if that is not what you want, could you please re-explain.

 

[Anthony86]

Ok sorry lets try again.

I'm copying/pasting a location & a unique identifier of a file to the target sheet. What I want the counter to do is, count the number of files against that 1 location.

The location is a textbox on my userform that holds the location until I clear it.

Make sense?

 

[Fluff]

You can do that like this

Cnt = WorksheetFunction.CountIf(wsSource.Range("A:A"), txtFileNumber.Value)

Simply change the range & textbox to suit