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

 

[Fluff]

Untested, but try

   Dim NxtRw As Long
   NxtRw = Range("A" & Rows.Count).End(xlUp).Offset(1).Row
   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("B" & NxtRw).PasteSpecial xlPasteValues
      .Offset(1).Columns(4).SpecialCells(xlVisible).Copy
      Range("C" & NxtRw).PasteSpecial xlPasteValues
      .Offset(1).Columns(5).SpecialCells(xlVisible).Copy
      Range("D" & NxtRw).PasteSpecial xlPasteValues
      Range(Range("A" & NxtRw), Range("B" & Rows.Count).End(xlUp).Offset(, -1)) = txtBoxNumber.Value
   End With

 

[Anthony86]

Untested, but try

   Dim NxtRw As Long
   NxtRw = Range("A" & Rows.Count).End(xlUp).Offset(1).Row
   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("B" & NxtRw).PasteSpecial xlPasteValues
      .Offset(1).Columns(4).SpecialCells(xlVisible).Copy
      Range("C" & NxtRw).PasteSpecial xlPasteValues
      .Offset(1).Columns(5).SpecialCells(xlVisible).Copy
      Range("D" & NxtRw).PasteSpecial xlPasteValues
      Range(Range("A" & NxtRw), Range("B" & Rows.Count).End(xlUp).Offset(, -1)) = txtBoxNumber.Value
   End With

Wow! You sir are a genius!

This worked great, thank you.

 

[Fluff]

Glad to help & thanks for the feedback

 

[Anthony86]

Glad to help & thanks for the feedback

Another question. How can I edit the code so, if a file isn't found in the auto filter then add the textbox value to the new workbook that opened and then continue as normal.

 

[Fluff]

Try

   Dim NxtRw As Long
   Dim Cnt As Long
   
   NxtRw = Range("A" & Rows.Count).End(xlUp).Offset(1).Row
   Cnt = WorksheetFunction.CountIf(wsSource.Range("A:A"), txtFileNumber.Value)
   If Cnt = 0 Then
      Range("A" & NxtRw).Value = txtBoxNumber.Value
   Else
      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("B" & NxtRw).PasteSpecial xlPasteValues
         .Offset(1).Columns(4).SpecialCells(xlVisible).Copy
         Range("C" & NxtRw).PasteSpecial xlPasteValues
         .Offset(1).Columns(5).SpecialCells(xlVisible).Copy
         Range("D" & NxtRw).PasteSpecial xlPasteValues
         Range(Range("A" & NxtRw), Range("B" & Rows.Count).End(xlUp).Offset(, -1)) = txtBoxNumber.Value
      End With
   End With

 

[Anthony86]

Try

   Dim NxtRw As Long
   Dim Cnt As Long
   
   NxtRw = Range("A" & Rows.Count).End(xlUp).Offset(1).Row
   Cnt = WorksheetFunction.CountIf(wsSource.Range("A:A"), txtFileNumber.Value)
   If Cnt = 0 Then
      Range("A" & NxtRw).Value = txtBoxNumber.Value
   Else
      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("B" & NxtRw).PasteSpecial xlPasteValues
         .Offset(1).Columns(4).SpecialCells(xlVisible).Copy
         Range("C" & NxtRw).PasteSpecial xlPasteValues
         .Offset(1).Columns(5).SpecialCells(xlVisible).Copy
         Range("D" & NxtRw).PasteSpecial xlPasteValues
         Range(Range("A" & NxtRw), Range("B" & Rows.Count).End(xlUp).Offset(, -1)) = txtBoxNumber.Value
      End With
   End With

Hmm I'm getting an error with this other line now.

'wsSource.ShowAllData' - I was using this to remove the filter each time.

 

[Fluff]

Very difficult to help when that line of code doesn't appear in any of the code on this thread.

 

[Anthony86]

Very difficult to help when that line of code doesn't appear in any of the code on this thread.

Dim wsSource As Worksheet    Dim wsTarget As Worksheet
    Dim nextRow As Long
    Dim NxtRw 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 = Range("A" & Rows.Count).End(xlUp).Offset(1).Row
   Cnt = WorksheetFunction.CountIf(wsSource.Range("A:A"), txtFileNumber.Value)
   If Cnt = 0 Then
      Range("A" & NxtRw).Value = txtBoxNumber.Value
      Range("B" & NxtRw).Value = txtFileNumber.Value
   Else
      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("B" & NxtRw).PasteSpecial xlPasteValues
         .Offset(1).Columns(4).SpecialCells(xlVisible).Copy
         Range("C" & NxtRw).PasteSpecial xlPasteValues
         .Offset(1).Columns(5).SpecialCells(xlVisible).Copy
         Range("D" & NxtRw).PasteSpecial xlPasteValues
         Range(Range("A" & NxtRw), Range("B" & Rows.Count).End(xlUp).Offset(, -1)) = txtBoxNumber.Value
      End With
      End If


[COLOR=#ff0000]      wsSource.ShowAllData[/COLOR]
      txtFileNumber.Value = Null
      txtFileNumber.SetFocus
    
    End Sub

I use the line in red to turn the filter off in the main spreadsheet.

 

[Fluff]

Not sure why that would start failing, as the previous code shouldn't affect it.
What happens if you change it to

   wsSource.AutoFilterMode = False