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]

Ok that worked, but now its setting the target workbook as the active one? I need to keep the source workbook open/Active.

 

[Fluff]

If the target workbook is not the active workbook then your code wont work.
When you open a workbook, it automatically becomes the active workbook.

 

[Anthony86]

Hmm, it worked before the last bit of code you gave me.

    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

How do I make the source workbook the active one again? or would 'Application Go To' work?

 

[Fluff]

Add

wsSource.activate

as the last line of code.

 

[Anthony86]

Add

wsSource.activate

as the last line of code.

Yeah just worked that out... Once again, Thank You!!

 

[Fluff]

You're welcome

 

[Anthony86]

Anyway of changing the code or making it faster? Seems to be really slow now.

 

[Fluff]

Not sure it will make much difference, but try

Sub chk()
   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
      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).Resize(, 3).SpecialCells(xlVisible).Copy
         wsTarget.Range("B" & NxtRw).PasteSpecial xlPasteValues
         wsTarget.Range(wsTarget.Range("A" & NxtRw), wsTarget.Range("B" & Rows.Count).End(xlUp).Offset(, -1)) = txtBoxNumber.Value
      End With
   End If
   
   
   wsSource.AutoFilterMode = False
   txtFileNumber.Value = Null
   txtFileNumber.SetFocus
   
End Sub

 

[Anthony86]

I tested it once and it seems to work better but its not copying the correct information.

I need columns A, D, E to be copied an pasted over.

 

[Fluff]

Missed that bit, in that case try

      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
         wsTarget.Range(wsTarget.Range("A" & NxtRw), wsTarget.Range("B" & Rows.Count).End(xlUp).Offset(, -1)) = txtBoxNumber.Value
      End With