0

How do I shorten this code to repeat the task, but take less space? This code needs to run for like 27 more users. I know I am a total noob right now, but this would ease my workload for more than an hour daily...

Workbooks.Open ("https://blabla/Edgars/2017 Q3Q4/Edgars.xlsx")
Workbooks("Edgars.xlsx").Activate

Range("C24:AN27").Select
Selection.Copy
Workbooks("abc.xlsx").Activate
Sheets("Edgars").Activate
Range("C24:AN27").Select
ActiveSheet.Paste
Workbooks("Edgars.xlsx").Activate
Sheets("Edgars").Activate
Range("C33:AN36").Select
Selection.Copy
Workbooks("abc.xlsx").Activate
Sheets("Edgars").Activate
Range("C33:AN36").Select
ActiveSheet.Paste
Workbooks("Edgars.xlsx").Activate
Sheets("Edgars").Activate
Range("C42:AN45").Select
Selection.Copy
Workbooks("abc.xlsx").Activate
Sheets("Edgars").Activate
Range("C42:AN45").Select
ActiveSheet.Paste

Workbooks("Edgars.xlsx").Activate
Sheets("Edgars").Select
ActiveWindow.SelectedSheets.Delete
Workbooks("abc.xlsx").Activate
Sheets("Edgars").Select
Sheets("Edgars").Copy Before:=Workbooks("Edgars.xlsx"). _
    Sheets(1)
Workbooks("Edgars.xlsx").Activate
Range("A1").Select
ActiveWorkbook.SaveAs Filename:="https://blabla/Edgars/2017 Q3Q4/Edgars.xlsx"
ActiveWorkbook.Close
Workbooks("abc.xlsx").Activate
Worksheets("TOOLS").Activate

Application.DisplayAlerts = True
Else
End If
Rolands Ignatjevs
  • 11
  • 2
  • You should add some more information, briefly descriping the purpose of your code (and/or add some comments). Also it is not clear to me, what you mean by "Shorten". Is there multiple files, wich needs to be run through the code? And if yes, is there some sort of naming structure? – Jakob Busk Sørensen Jun 26 '17 at 12:19
  • 1
    https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – Siddharth Rout Jun 26 '17 at 12:21
  • What exactly is taking you an hour? Running the code or adapting the values within the code? Using a loop of any sort will very likely not result in any notable change of execution speed. –  Jun 26 '17 at 12:23
  • Thank you for your comments! What I mean is that this code (same thing, different files) needs to repeat for 20 more users with different usernames (of course). I started digging into the article Siddharth posted and am working on shortening the thing. An hour or more would it take me to copy everything manually for those people :) – Rolands Ignatjevs Jun 26 '17 at 13:37

1 Answers1

1

Before you start tackling loops, or shortening the code, you will want to brush up on avoiding Select and Activate within VBA. Your code, in its current form, is difficult to read or improve because you are using a lot of implicit relationships.

For example:

Workbooks("Edgars.xlsx").Activate
Sheets("Edgars").Activate

Is actually:

Application.Workbooks("Edgars.xlsx").Activate
ActiveWorkbook.Sheets("Edgars").Activate

And can be changed to:

Application.Workbooks("Edgars.xlsx").Worksheets("Edgars").Activate

Of course, this doesnt eliminate the Activate problem, and you'll undoubtedly encounter an issue if 'Edgars.xlsx' is not an open workbook. This example isnt meant to solve your problem, it is only mean to point you in the right direction.

Once you refactor your code to eliminate the use of Activate, Select and the Implicit (unqualified) references, you can get yourself started with string variables. Something like this is valid code:

Dim UserName as String
UserName = "Edgars"
Application.Workbooks(UserName & ".xlsx").Worksheets(UserName).Activate

This allows your code greater flexibility, and gets you one step closer.

My recommendation would be to start here: How to avoid using Select in Excel VBA macros. Once you read this post, try implementing these steps within your code. Then read some more and try again.

Learning proper VBA takes a bit of a time investment, but if you can learn how to code VBA well you can save yourself much more time than just an hour (assuming you use Excel for more than just one process).

Best of luck!

Brandon Barney
  • 2,382
  • 1
  • 9
  • 18