Solved: 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?

[VBA]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[/VBA]

thanks for helping!

[HaHoBe]

Hi, Klartigue,

shouldn´t it be

[vba].Cells(LastRow + 1, "A").Paste [/vba]
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]

[VBA]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[/VBA]

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

[frank_m]

Try replacing
[vba].Cells(LastRow + 1, "A").Paste [/vba]
With
[vba]
.Cells(LastRow + 1, "A").Select
.Paste
[/vba]

[Klartigue]

[VBA]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[/VBA]

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]

[VBA]Sub Copy()
Dim LastRow As Long

With ActiveSheet

.Range("A2:I2").Resize(.Range("A2").End(xlDown).Row - 1).Copy
End With
End Sub [/VBA]

[VBA]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 [/VBA]

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?
[VBA]
.Paste
Application.Run "BLPLinkReset"
[/VBA]

[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:

[VBA]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[/VBA]

[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[vba]
LastRow = Workbooks("Broker Volume Master.xlsx"). _
Worksheets("Sheet1").Range("A2").End(xlDown).Row
' change Sheet1 to your Sheet Name
[/vba]
Another approach
[vba]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[/vba]

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

[VBA]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[/VBA]

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:
[vba]

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
[/vba]

[Klartigue]

I fixed it, this code works:

[VBA]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[/VBA]


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),"")