Problem with macro

There is a problem with the highlighted line. I have tried to alter this but can't figure it out. Can you tell what is wrong?

Code:

---

Sub CopyandPaste()
    Dim LastRow As Long
    With ActiveSheet
        .Range("A2:H2").Resize(.Range("A2").End(xlDown).Row - 1).Copy
    End With
    Windows("Broker Volume Master.xlsx").Activate
    Application.Run "BLPLinkReset"
    With ActiveSheet
        LastRow = .Range("A2").End(xlDown).Row
        .Cells("A", LastRow + 1).Paste
    End With
End Sub

---

thanks for helping!

Hi, Klartigue,

shouldn´t it be

Code:

---

.Cells(LastRow + 1, "A").Paste

---

Rownumber at first, Columnnumber second.

Ciao,
Holger

That still doesnt work..I think maybe its something about being mismatched.

Code:

---

LastRow = .Range("A2").End(xlDown).Row
                .Cells("A", LastRow + 1).Paste


---

It ends with .Row and starts the next line with .Cell ???

Hi, Klartique,

Cells expects the Row-number as the first argument and the Column-idenfication (may be either a number or text) as the second - please change it in your code as pointed out in my example.

Ciao,
Holger

Code:

---

Sub CopyandPaste()
    Dim LastRow As Long
    With ActiveSheet
        .Range("A2:H2").Resize(.Range("A2").End(xlDown).Row - 1).Copy
    End With
    Windows("Broker Volume Master.xlsx").Activate
    Application.Run "BLPLinkReset"
    With ActiveSheet
        LastRow = .Range("A2").End(xlDown).Row
        .Cells(LastRow + 1, "A").Paste
    End With
    End Sub

---

I tried it this way and it says "Object doesnt support this property or method"

Try replacing

Code:

---

.Cells(LastRow + 1, "A").Paste

---

With

Code:

---


.Cells(LastRow + 1, "A").Select
.Paste

---

Code:

---

Sub CopyandPaste()
    Dim LastRow As Long
    With ActiveSheet
        .Range("A2:I2").Resize(.Range("A2").End(xlDown).Row - 1).Copy
    End With
    Windows("Broker Volume Master.xlsx").Activate
    Application.Run "BLPLinkReset"
    With ActiveSheet
        LastRow = .Range("A2").End(xlUp).Row
        .Cells(LastRow + 1, "A").Select
        .Paste
    End With
    End Sub

---

The only problem with this now is that it doesn't paste my copied cells:

Code:

---

.Range("A2:I2").Resize(.Range("A2").End(xlDown).Row - 1).Copy

---

Thanks for helping me out with this

Code:

---

Sub Copy()
    Dim LastRow As Long
    With ActiveSheet
        .Range("A2:I2").Resize(.Range("A2").End(xlDown).Row - 1).Copy
    End With
End Sub
 
Sub Paste()
    Windows("Broker Volume Master.xlsx").Activate
    Application.Run "BLPLinkReset"
    With ActiveSheet
        LastRow = .Range("A2").End(xlUp).Row
        .Cells(LastRow + 1, "A").Select
        .Paste
    End With
    End Sub

---

I have broken it up into two pieces. The first one works just fine. I just need those copied values to be pasted into the line after the last line in the broker volume worksheet.

Would it be ok to run the macro after the .Paste command?

Code:

---


.Paste
Application.Run "BLPLinkReset"

---

Hi there,

It is very doubtful that we'd need to be activating a window. Also, if there are any skipped cells, xldown may put you in the wring place. As written, try:

Code:

---

Sub CopyAndPaste()
    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("A2:H" & LastRow).Copy
        Windows("Broker Volume Master.xlsx").Activate
        Application.Run "BLPLinkReset"
        .Paste Workbooks("TestDest.xls").ActiveSheet.Range("A2").End(xlDown).Offset(1)
    End With
End Sub

---

Hi (GTO) Mark

I believe that the routine is meant to copy from workbook to another.
(I guess that from seeing the command: Workbooks("Broker Volume Master.xlsx").Activate before the LastRow is set and data pasted.)
In which case to use your version, the LastRow line of code needs to be changed to something like

Code:

---


LastRow = Workbooks("Broker Volume Master.xlsx"). _
Worksheets("Sheet1").Range("A2").End(xlDown).Row
' change Sheet1 to your Sheet Name

---

Another approach

Code:

---

Sub CopyPaste()
    Dim LastRow As Long
    LastRow = Workbooks("Broker Volume Master.xlsx"). _
    Worksheets("Sheet1").Range("A2").End(xlDown).Row
    ' change Sheet1 to your Sheet Name
    With ActiveSheet
        .Range("A2:H2").Resize(.Range("A2").End(xlDown).Row - 1).Copy _
        Destination:=Workbooks("Broker Volume Master.xlsx").Worksheets("Sheet1"). _
        Range("A" & LastRow + 1) ' change Sheet1 to your Sheet Name
    End With
    Workbooks("Broker Volume Master.xlsx").Activate
    Application.Run "BLPLinkReset"
End Sub

---

I have to appoligize to you Mark. After re-examining your code, I see now that it looks fine.

--I was mentaly thrown off because in the original code, the LastRow variable was used in the destination workbook for the .Paste operation; Whereas in your version it is used for copying.

:bow: please foregive me. And also thank you much, for not barking back at me to tell me what a fool I am :whip

Hi Frank,

Shucks, no problem. I'm still not utterly sure, since we have no idea what's happening in BLPLinkReset().

Besides, I usually travel between two states. Arizona and the state of confusion!

Yes, I am trying to copy the below range from Volume worksheet.xlsx

.Range("A2:H2").Resize(.Range("A2").End(xlDown).Row - 1).Copy _
from the


And place this copied material in Broker Volume Master.xlsx and have it be placed in the next available row

LastRow = .Range("A2").End(xlUp).Row
.Cells(LastRow + 1, "A").Select
.Paste

But things are still not working..

The following code gets me a little closer to the correct final product..

Code:

---

Sub CopyAndPaste()
    Dim LastRow As Long
    Windows("Volume worksheet.xlsx").Activate
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("A2:H" & LastRow).Copy
        Windows("Broker Volume Master.xlsx").Activate
        Application.Run "BLPLinkReset"
        .Paste Workbooks("Broker Volume Master.xlsx").ActiveSheet.Range("A2").End(xlDown).Offset(1)
    End With
End Sub

---

However, please see attachment. The data in cells 180 to 187 should be pasted in A2 (which is the next available row). And all those N/A in column A and brus in column E should not be there.

Thanks for the help, almost there

It is just a thought, but mayhap if you were to respond directly to each suggestion and say in what way it does not work ("does not work" by itself does not readily aid resolution), the situation might be resolved more easily.

In my last post, I attached the outcome and explained what I would want to see differently.

True, but without the source file, we do not know what you were copying so it's hard to say what should have been there (or elsewhere) instead.
At present my best guess would be something like:

Code:

---

Sub CopyAndPaste()
    Dim LastRow          As Long
    Dim wks              As Excel.Worksheet
    Windows("Volume worksheet.xlsx").Activate
    Set wks = ActiveSheet
    With wks
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Windows("Broker Volume Master.xlsx").Activate
        Application.Run "BLPLinkReset"
        .Range("A2:H" & LastRow).Copy
        Workbooks("Broker Volume Master.xlsx").ActiveSheet.Range("A2").End(xlDown).Offset(1).PasteSpecial xlPasteValues
    End With
End Sub

---

I fixed it, this code works:

Code:

---

Sub CopyAndPaste()
    Dim LastRow As Long
    Windows("Volume worksheet.xlsx").Activate
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("A2:I2").Resize(.Range("A2").End(xlDown).Row - 1).Copy
        Windows("Broker Volume Master.xlsx").Activate
        .Paste Workbooks("Broker Volume Master.xlsx").ActiveSheet.Range("A2:I2").End(xlUp).Offset(1)
        Application.Run "BLPLinkReset"
    End With
End Sub

---

One question, if you see the attached, I there is a ton of "#N/A" in column A and "brus" in column E that I do not want. Do you know how i would alter my code to get rid of those?

Change the formula to

Code:

---

=IFERROR(VLOOKUP(F2,'C:\Charles Luke\Back Office\Security Info\[sec.info.xlsx]sec'!$1:$1048576,2,FALSE),"")

---