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?

You should check this post on **[avoiding using select](http://stackoverflow.com/a/10717999/2687063)**. — simpLE MAn, Mar 12 '14 at 20:04

Automate This · Accepted Answer · 2014-03-12T22:12:46.660

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

Thanks! So for example, how would I write something like: For Cells Find([keyphrase in ColA]) to lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row select range from Cells Find([keyphrase in ColA]) to lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row for columns A,B,C,D,E — 114, Mar 12 '14 at 21:53
I edited the answer to include an example of what I think your asking for. You might want to ask a new question for these instead of making one really long question. Hope this helps. — Automate This, Mar 12 '14 at 22:13
That's a good point, I will do that in future questions. I see how I could link this up for multiple keyphrases now quite easily. One final question just to append to that last example: How would one insert this into a loop that performs this on multiple Excel files. In fact, this is probably exactly the type of question you suggested I should make a new form to ask! — 114, Mar 12 '14 at 22:19
:-) Yes, please submit a new question for that but first do a search as there are many examples of looping through workbooks. Please mark as answered if I've covered your original post. Thanks. — Automate This, Mar 12 '14 at 22:22

Rearranging Data in Excel with a Set Format

1 Answers1

Linked