Run-time error ("Wrong number of arguments...)

[thardin]

I'm all of a sudden getting a Run-time error ("Wrong number of arguments ...) with this code.

How do I fix this.

Sub test()
'
'
' Keyboard Shortcut: Ctrl+Shift+P
'
With ActiveSheet
    .Range("A:D" & Rows.Count, "L" & Rows.Count, "P" & Rows.Count, "T" & Rows.Count).End(xlUp).Copy
End With
    Workbooks.Add
    
    Worksheets("Sheet1").Paste
    
    With ActiveSheet
        Range("H1") = "Deleted?"
    
    End With
    
End Sub

 

[Joe4]

Yep, you have some issues with the construction of this line here:

   .Range("A:D" & Rows.Count, "L" & Rows.Count, "P" & Rows.Count, "T" & Rows.Count).End(xlUp).Copy

I find it better to build the range as a string first so you can easily using a MsgBox to verify that you construcuted it correctly, like this:

Sub test()
'
'
' Keyboard Shortcut: Ctrl+Shift+P
'
Dim s As String

s = "A" & Rows.Count & ":D" & Rows.Count & ",L" & Rows.Count & ", P" & Rows.Count & ", T" & Rows.Count

With ActiveSheet
    .Range(s).End(xlUp).Copy
End With
    Workbooks.Add
   
    Worksheets("Sheet1").Paste
   
    With ActiveSheet
        Range("H1") = "Deleted?"
   
    End With
   
End Sub

If that does not do what you need, please explain to us (in plain English) exactly what it is you are trying to accomplish.
It is kind of off to place the ".End(xlUp)" part on a whole range of disjointed columns.
It is more common to do it on one column, and apply that value to all other range references afterwards.
If we need to go that route, we just need you to tell us which column we can look at to determine the last row of data.

 

[thardin]

I am trying to copy columns A:D, L, P, and T from one workbook and paste into a new workbook.

 

[Joe4]

Did my updated code do what you want?
What column can we use in your original data to determine exactly where the last row of data resides?

 

[thardin]

Did my updated code do what you want?
What column can we use in your original data to determine exactly where the last row of data resides?

Sorry, but no, I didn't.
It only copied the last row of column A and pasted it in A1 of the new workbook. And assigned H1

 

[Joe4]

Ok, we really need you to be clearer about what you are trying to do and answer all the questions asked.
Remember, we know nothing about your project or your data structure. We only have to work with whatever you decide to share with us.

So I will ask for a third time:

What column can we use in your original data to determine exactly where the last row of data resides?

And regarding this:

I am trying to copy columns A:D, L, P, and T from one workbook and paste into a new workbook.

Are you trying to copy them to the SAME columns?
Because I believe that by default. Excel will paste column L right after column D, leaving no blanks in between.

If you are having a hard time describing, maybe post some images of what your data looks like, and what you want your expected result to look like.

 

[thardin]

I am trying to copy all the data in Columns A:D, L, P and T and paste it into a new workbook in the 1st 5 columns, like this:

 

[Joe4]

Thank you. That helps clarify things.

Try this:

Sub test()
'
'
' Keyboard Shortcut: Ctrl+Shift+P
'
    Dim lr As Long
    Dim s As String

'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row

'   Build range string
    s = "A1:D" & lr & ",L1:L" & lr & ",P1:P" & lr & ",T1:T" & lr

'   Copy data
    With ActiveSheet
        .Range(s).Copy
    End With
    
    Workbooks.Add
    
    Worksheets("Sheet1").Paste
    
    With ActiveSheet
        Range("H1") = "Deleted?"
    End With
    
End Sub

 

[thardin]

Thank you. That helps clarify things.

Try this:

Sub test()
'
'
' Keyboard Shortcut: Ctrl+Shift+P
'
    Dim lr As Long
    Dim s As String

'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row

'   Build range string
    s = "A1:D" & lr & ",L1:L" & lr & ",P1:P" & lr & ",T1:T" & lr

'   Copy data
    With ActiveSheet
        .Range(s).Copy
    End With
   
    Workbooks.Add
   
    Worksheets("Sheet1").Paste
   
    With ActiveSheet
        Range("H1") = "Deleted?"
    End With
   
End Sub

That Worked!
Thanks

 

[Joe4]

You are welcome.
I hope it makes sense how I apporached it.