PDA

View Full Version : Solved: Problem with macro



Klartigue
09-16-2011, 11:32 AM
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
09-16-2011, 12:07 PM
Hi, Klartigue,

shouldn´t it be

.Cells(LastRow + 1, "A").Paste
Rownumber at first, Columnnumber second.

Ciao,
Holger

Klartigue
09-16-2011, 12:13 PM
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
09-16-2011, 12:19 PM
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
09-16-2011, 01:32 PM
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
09-16-2011, 02:08 PM
Try replacing
.Cells(LastRow + 1, "A").Paste
With

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

Klartigue
09-16-2011, 02:12 PM
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
09-16-2011, 02:16 PM
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
09-16-2011, 02:20 PM
Would it be ok to run the macro after the .Paste command?

.Paste
Application.Run "BLPLinkReset"

GTO
09-16-2011, 02:26 PM
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
09-16-2011, 02:59 PM
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
09-16-2011, 06:45 PM
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

GTO
09-16-2011, 07:57 PM
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
09-19-2011, 07:28 AM
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
09-19-2011, 07:37 AM
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
09-19-2011, 08:11 AM
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
09-19-2011, 08:26 AM
In my last post, I attached the outcome and explained what I would want to see differently.

Aflatoon
09-19-2011, 08:31 AM
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
09-19-2011, 09:42 AM
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
09-19-2011, 03:28 PM
Change the formula to

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

Klartigue
09-20-2011, 07:58 AM
thank you!