Problem with macro

[Klartigue]

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?

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!

[HaHoBe]

Hi, Klartigue,

shouldn´t it be

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

Rownumber at first, Columnnumber second.

Ciao,
Holger

[Klartigue]

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

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

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

[HaHoBe]

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

[Klartigue]

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"

[frank_m]

Try replacing

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

With

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

[Klartigue]

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:

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

Thanks for helping me out with this

[Klartigue]

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.

[frank_m]

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

 
.Paste
Application.Run "BLPLinkReset"

[GTO]

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:

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

[frank_m]

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

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

Another approach

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

[frank_m]

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.

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

[GTO]

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!

[Klartigue]

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..

[Klartigue]

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

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

[Aflatoon]

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.

[Klartigue]

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

[Aflatoon]

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:

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

[Klartigue]

I fixed it, this code works:

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?

[Bob Phillips]

Change the formula to

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