Forum Discussion
To run a macro on another excel sheet with a different number of rows
I'd have to se a copy of both workbooks.
Hi Hans,
I have produced two small examples.
Load Classf.xlsx and run the macro below.
This will load WAL.xlsx and it is then supposed to copy all data from the sheet "ClassB_List" in Classf.xlsx and paste it at the end of the existing data in the sheet "Allocation List" in WAL.xslx.
However the first execution of the macro seems to load WAL.xlsx but does nothing more until you run the macro again and everything is then okay.
Do you have any ideas what the problem may be?
Sub UpdateLists()
Dim l2Row As Long
Dim BNRows1 As Long
Application.ScreenUpdating = False
Workbooks("Classf.xlsx").Activate
Workbooks("Classf.xlsx").Sheets("ClassB_List").Activate
BNRows1 = ActiveSheet.Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
Workbooks.Open "C:\Home\Coding\WAL.xlsx"
Workbooks("WAL.xlsx").Activate
Workbooks("WAL.xlsx").Sheets("Allocation List").Activate
l2Row = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
l2Row = l2Row + 1
Workbooks("Classf.xlsx").Worksheets("ClassB_List").Range("A2", "K" & BNRows1).Copy _
Workbooks("WAL.xlsx").Worksheets("Allocation List").Range("A" & l2Row)
Range("A2").Select
Workbooks("Classf.xlsx").Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub
- Ok I now know what the cause is - assigning a shortcut key to the macro.
Even with your code, assigning a shortcut key such as CTRL SHFT U makes the code only work if run twice. I don't know why! I cannot explain that.
P.S. This discussion has become very long and awkward to navigate.
If you have a new question unrelated to the current discussion, would you be so kind to start a new discussion? Thanks in advance.
- Sorry Hans
I did have a typo and your code now works in PERSONAL.XLSB
Why my code needs the dual run is a mystery? In that case, I have no idea why it doesn't work, but you could try
Set wbkClass = ActiveWorkbookas in your original code.
- Yes
Are you sure that your workbook is named Classf.xlsx and that it is open in Excel?
- Nope same error.
Runs perfectly if I remove Personal.XLSB from my XLSTART Yes, that requires a change to the code. Change the line
Set wbkClass = ThisWorkbookto
Set wbkClass = Workbooks("Classf.xlsx")- Thanks Hans,
However when I put your code into a module within my PERSONAL.XLSB there are errors.
This was where my own macro sat and had the unusual two pass requirement. Could placing the macro within PERSONAL.XLSB cause issues? Your code now produces a runtime error 9 I streamlined the code a bit. It works on the first run for me...
See the attached version (now a macro-enabled workbook).