VBA to select columns for range

[RChapman9]

I have a pivot table with inventory reports for a number of schools. I need to copy each school's section individually from a pivot table and paste on another sheet. Once I find the starting point for a particular school, I can use xlDown to set the correct number of rows. However, I need the "Range("A5:N5")" to be dynamic, based on the starting point. I can't use xlRight because not all the columns are filled. If the starting point is A5, how do I tell it to select A5:N5? For A18, how to select A18:N15, etc?

    Range("A5:N5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Application.CutCopyMode = False
    Range("A18:N18").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Application.CutCopyMode = False
    Sheets("Solutions by Location (2)").Select

Thanks so much!!

 

[My Aswer Is This]

Using select is not a good plan.
And your just copying the data
Where do you plan to past the data?
Anther sheet is not a detailed example
Do you want the script do the pasting?

Please explain in more detail what your ultimate goal here is.

And this is not a proper range:
You said:

A18:N15
Did you mean

A18:N18​

 

[My Aswer Is This]

Maybe this will help:

Selection.Resize(, 14).Copy

 

[RChapman9]

So sorry for the typo--it should indeed be A18:N18. That was intended as a second example--should have had a break between the snippets of code. I have the code for pasting, just need the proper copy range.

This is from recording the macro--that's why "select" is used. I'm searching for a better solution, but I've tried 6 different things with no luck. Basically, once I land on a starting cell, whether it's A5 or A18 or A100, how do I select 13 columns across?

(I am unable to post a shot of the table because I can't use add-ins at work.)

Thank you so much!!

 

[My Aswer Is This]

Did you try my code?
Change 14 to 13

 

[RChapman9]

Did you try my code?
Change 14 to 13

The problem is getting the selection of columns. I don't want to select A5:N5. I want to land on A5 (or any other cell in column A) and have it expand the selection over by 13 columns. It seems like it would be something like "R1C1:R" & lastrow & "C" & 13, or some such dynamic selection. I just don't know the proper choice and syntax.

Thank you so much for continuing to assist!!

 

[My Aswer Is This]

You did not answer my question
Did you try my line of code

If your active cell in C1 then C1 to C 13 are copied.

If your active cell is D1 then D1 to D13 are copied

 

[RChapman9]

Yes, it didn't work initially but then I figured out the necessary tweak.

Selection.Resize(, 14).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

Thank you so much! I truly appreciate your help!!

 

[My Aswer Is This]

Try this:

Selection.Resize(, 13).Select
Range(Selection, Selection.End(xlDown)).Copy

 

[My Aswer Is This]

I finally realized what you wanted:

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.