Rearranging Data in Excel with a Set Format

Question

The following is a recorded macro:

 Range("A5:E11").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A61:E77").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("[SHEET NAME]").Select
    ActiveWindow.SmallScroll Down:=15
    Range("A80:B88").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("[SHEET NAME]").Select
    ActiveWindow.SmallScroll Down:=12
    Range("A91:J114").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Sheets("[SHEET NAME]").Select

I was wondering how to change the sheet name in capital letters so that the macro will run the same on any workbook. That is, how do I reference the sheet based on its location?

As a secondary and more complicated issue, the above code copies three tables to three separate worksheets. However, the number of tables and the size of those tables are variable. How can I tell Excel to stop highlighting a range once, say, two cells without text are found and at that point copy and paste the selected range a fixed number of cells out to a new sheet and name it based on the sheet it is coming from. Further, how can I make the macro move down after this until it sees text and then begin highlighting another table to copy and paste?

Answer 1

I agree with @simpLEMAn, you should re-write to remove Select and Active statements.

To answer your first question, set the destination worksheet like this:

Dim wk As Worksheet
Set wk = Worksheets("MySheetName")

Now you can do things with that sheet like the following without having to Select or Activate via code:

wk.Range("A1").value = "Test"

---

If the sheet is the current sheet when the macro is run but the name might be different in different workbooks you can change the definition to this:

Dim wk As Worksheet
Set wk = ActiveSheet

---

The second question involves finding the last cell in a range. There are many post related to this and depends a little bit on your data format. Basically, you will create a new variable and assign it the value of the last cell and then use this variable in your code.

For example, last row with content in column E can be found like this:

lngLastRow = Cells(Rows.Count, "E").End(xlUp).Row

And now you can copy a dynamic range like this:

wk.Range("A5:E" & lngLastRow).Copy

---

Per your question in the comments, here is an example of how to find a key phrase in column A and then select everything from that phrase down to the lost row in column A.

Dim wk As Worksheet
Set wk = Worksheets("Sheet1")

FirstRowColA = Application.WorksheetFunction.Match("keyphrase", wk.[A:A])
LastRowColA = Cells(wk.Rows.Count, "A").End(xlUp).Row

wk.Range("A" & FirstRowColA & ":A" & LastRowColA).Copy

Worksheets("Sheet2").Paste