Copy and paste not the same size

[Tucker92]

I have been trying to copy and paste selected filtered columns from one work book to another but i keep getting the same error about them not being the same size. I can copy and paste them manually but it wont let me do it through my vba code.

Can anyone please take a look and let me know if it is possible and what i have done wrong

Thank you

Here is my code

 Sub Copy_12Met_WTL()

Dim ws As Worksheet
Dim Vac As Worksheet
    Set Vac = Application.Workbooks("Vac AC").Sheets("12 Metal")
    Set ws = Application.ActiveSheet


Dim str As String
    str = "12""MET"
          
        
   ws.Range("WTL").AutoFilter field:=3, Criteria1:="=*" & str & "*", VisibleDropDown:=True
If ws.ListObjects("WTL").Range.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
            
    Dim data_end_row_number As Long
    Dim nxt As Long
     data_end_row_number = ws.Range("A3").End(xlDown).Row
     nxt = Vac.Range("A2").End(xlDown).Row + 1
     
            ws.Range("D" & data_end_row_number).SpecialCells(xlCellTypeVisible).copy Destination:=Workbooks("Vac AC").Sheets("12 Metal").Range("B" & nxt)
            ws.Range("A" & data_end_row_number).SpecialCells(xlCellTypeVisible).copy Destination:=Workbooks("Vac AC").Sheets("12 Metal").Range("A" & nxt)
            ws.Range("E" & data_end_row_number).SpecialCells(xlCellTypeVisible).copy Destination:=Workbooks("Vac AC").Sheets("12 Metal").Range("F" & nxt)
    
    Else
    MsgBox "No Carry Over For 12 Metal Line"
            End If
    End Sub

 

[Kamolga]

and with

[COLOR=#333333]ws.Range("D" & data_end_row_number).SpecialCells(xlCellTypeVisible).copy
[/COLOR]application.goto [COLOR=#333333]Workbooks("Vac AC").Sheets("12 Metal").Range("B" & nxt)
[/COLOR]activesheet.paste

?

 

[Tucker92]

Only issue is that because the workbook that im using has a different name each day and week it needs to stay as the active workbook and i don't know how to go back to it after

 

[Kamolga]

If you don't know future name, dim a workbook and when you are in this workbook without specific name, set your object as activeworkbook

Dim Wb As Workbook
Set Wb = ActiveWorkbook


'Your code from one workbook to another
Wb.ws.Range("D" & data_end_row_number).SpecialCells(xlCellTypeVisible).Copy
Application.Goto Workbooks("Vac AC").Sheets("12 Metal").Range("B" & nxt)
ActiveSheet.Paste


Wb.Activate

 

[Tucker92]

It still comes up with the same error when it goes to paste

 

[Tucker92]

this seems to work but copies the whole spread sheet, empty cells in all across down to the last row on the page and im not sure why

Sub Copy_12Met_WTL()

Dim ws As Worksheet
Dim Vac As Worksheet
Dim Wb As Workbook
    
    Set Wb = Application.ActiveWorkbook
    Set Vac = Application.Workbooks("Vac AC").Sheets("12 Metal")
    Set ws = Application.ActiveSheet


Dim str As String
    str = "12""MET"
          
        
   ws.Range("WTL").AutoFilter field:=3, Criteria1:="=*" & str & "*", VisibleDropDown:=True
If ws.ListObjects("WTL").Range.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
            
    Dim data_end_row_number As Long
    Dim nxt As Long
     
     data_end_row_number = ws.Range("A3").End(xlDown).Row
     nxt = Vac.Range("A2").End(xlDown).Row + 1
     
            ws.Range("A" & data_end_row_number).SpecialCells(xlCellTypeVisible).copy
            Workbooks("Vac AC").Sheets("12 Metal").Activate
            Range("A" & nxt).Select
            ActiveSheet.Paste
            
    
    Else
    MsgBox "No Carry Over For 12 Metal Line"
            End If
    End Sub

 

[Kamolga]

Is your filter right? You filter on 12"MET without space between but a " and whatever comes before of after it?
Basically after te macro is run, the WTL range looks as you wish?

 

[Tucker92]

Yea the filter works fine and clears out what i dont want but then i copies all the columns instead of just the A and also it doesn't just go down to the row i want it copies all down to the bottom of the spreadsheet itself.

But when i go into debugging mode it gives the correct row numbers for both the data_end_row and nxt. Im very confused

 

[Kamolga]

Nxt is fine indeed

 

[Kamolga]

Yea the filter works fine and clears out what i dont want but then i copies all the columns instead of just the A and also it doesn't just go down to the row i want it copies all down to the bottom of the spreadsheet itself.

But when i go into debugging mode it gives the correct row numbers for both the data_end_row and nxt. Im very confused

Then the copy line is wrong.

If you need visible cells from A3 to the last cell

[COLOR=#333333]ws.Range(cells(3,[/COLOR][COLOR=#333333]"A" [/COLOR][COLOR=#333333]),cells(data_end_row_number,[/COLOR][COLOR=#333333]"A" )[/COLOR][COLOR=#333333]).SpecialCells(xlCellTypeVisible).copy[/COLOR]

should work