Macros and Named Ranges

[rob1987]

Hello,

I am having trouble writing code based around named ranges. I have a spreadsheet with tabs named "overview" "jan" "feb" "march" and so on.

In "overview" A1 I have A, in A3 i have B and in A5 I have C. In "jan" I have named ranges A1, B1 and C1. In "feb" named ranges A2, B2 and C2, In "march" I have named ranges A3, B3 and C3 and so on. All these ranges appear in columns f:g and are twenty rows long, although there are some blank rows at the bottom of some of the ranges.

What I want to be able to do is set up a Macro which will copy the the rows which contain text in range A1 and paste them into a range named A on the "overview" tab (in column B and C). This will also add rows in so that B will still appear in column A but still with a one row gap between the last row of the range A.
I then want a Macro which will copy the non-blank rows in range A2 and insert the appropriate number of rows onto the bottom of range A and paste the contents of A2 here following on from A1, moving B down as appropriate.

Basically I want to be able to copy and paste range 1 onto the end of range 2 and range 2 to expand to include the data pasted from range 1 so range 3 can then be pasted onto the end of there...

I hope that makes sense... please can somebody help?

 

[rob1987]

To put it more simpler maybe:

I know how to set up code to copy and paste range1 onto the end of rangeA but how do I expand Range A so it now includes the original contents of A and the new data copied in from 1?

 

[Andrew Poulsom]

You can't create the names A1, B1 and C1.

 

[VoG]

Maybe something like this

Range("A1:A100").Copy Destination:=Range("myName").Offset(1)
Range("myName").CurrentRegion.Name = "myName"

 

[Andrew Poulsom]

You can resize a name like this:

    With Range("Range1")
        .Resize(.Rows.Count + Range("Range2").Rows.Count).Name = "Range1"
    End With

 

[rob1987]

Hi Andrew, they were just example names

 

[Andrew Poulsom]

Hi Andrew, they were just example names

No they're not, any more than a labrador is an example of a cat.

 

[rob1987]

Okay.. for future reference how should I differentiate between example hyperthetical named ranges?

Thank you very much for the code, it works in expanding the range. Is there any way to copy only the cells containing data from the range and ignore the blank ones?

 

[Andrew Poulsom]

If you must use hypothetical names, use ones that are valid.

You can AutoFilter for NonBlanks and copy the visible cells.

 

[rob1987]

The A1, B1 example ranges could be confused with the cell references, i se now, thanks.