vba to move and renaming data within a workbook

[Ozzie via OfficeKB.com]

Guys,

I have a sheet of data, with many lines, that I want to seperate out onto
individual tabs within the same workbook, and to re-name those tabs.

ie within the master sheet their will be data for Area1, Area2, Area3 etc and
what I need is Sheet1 = Area1 - tab re-named Area1, Sheet2 = Area2 - tab re-
named to Area2 and so on.

Anybody know how to write the code to do this??

Many thanks for any help

Kind Regards

David

 

[Ron de Bruin]

See this page Ozzie
http://www.rondebruin.nl/copy5.htm

You must use this example
http://www.rondebruin.nl/copy5.htm#all

 

[Ozzie via OfficeKB.com]

Thanks Ron for your speedy reponse, the attached link works perfectly, and
I'm now using it,

one other thing though, is it possible to say;

insert 4 lines on each new tab and then

move range a1:c2 to d1, then delete columns a:c

I don't want to put any reference to sheet names, as they will change
depending upon the data extract.

Thanks again for your help Ron,

Regards

David

See this page Ozzie
http://www.rondebruin.nl/copy5.htm

You must use this example
http://www.rondebruin.nl/copy5.htm#all

Guys,

[quoted text clipped - 12 lines]

David

 

[Ron de Bruin]

Hi

Try this

Above this line

WSNew.Columns.AutoFit

you can add this two lines

WSNew.Range("A1:C2").Copy WSNew.Range("D1")
WSNew.Columns("A:C").Delete


--
Regards Ron de Bruin
http://www.rondebruin.nl

Thanks Ron for your speedy reponse, the attached link works perfectly, and
I'm now using it,

one other thing though, is it possible to say;

insert 4 lines on each new tab and then

move range a1:c2 to d1, then delete columns a:c

I don't want to put any reference to sheet names, as they will change
depending upon the data extract.

Thanks again for your help Ron,

Regards

David

See this page Ozzie
http://www.rondebruin.nl/copy5.htm

You must use this example
http://www.rondebruin.nl/copy5.htm#all

Guys,

[quoted text clipped - 12 lines]

David

 

[Ron de Bruin]

Oops

To insert the rows use

WSNew.Range("A1:A4").EntireRow.Insert




--
Regards Ron de Bruin
http://www.rondebruin.nl

Hi

Try this

Above this line

WSNew.Columns.AutoFit

you can add this two lines

WSNew.Range("A1:C2").Copy WSNew.Range("D1")
WSNew.Columns("A:C").Delete


--
Regards Ron de Bruin
http://www.rondebruin.nl

Thanks Ron for your speedy reponse, the attached link works perfectly, and
I'm now using it,

one other thing though, is it possible to say;

insert 4 lines on each new tab and then

move range a1:c2 to d1, then delete columns a:c

I don't want to put any reference to sheet names, as they will change
depending upon the data extract.

Thanks again for your help Ron,

Regards

David

See this page Ozzie
http://www.rondebruin.nl/copy5.htm

You must use this example
http://www.rondebruin.nl/copy5.htm#all

Guys,

[quoted text clipped - 12 lines]

David

 

[Ozzie via OfficeKB.com]

Morning Ron

Cheers for the code, inserting the additional lines and deleting the columns
works fines however the the pasting of the copied cells doesn't seem to work?
any ideas.

Cheers for your help,

David

Oops

To insert the rows use

WSNew.Range("A1:A4").EntireRow.Insert

Hi

[quoted text clipped - 38 lines]

 

[Ron de Bruin]

Hi Ozzie

Do you want to copy the cells from ws1 to the sheets?


--
Regards Ron de Bruin
http://www.rondebruin.nl

Morning Ron

Cheers for the code, inserting the additional lines and deleting the columns
works fines however the the pasting of the copied cells doesn't seem to work?
any ideas.

Cheers for your help,

David

Oops

To insert the rows use

WSNew.Range("A1:A4").EntireRow.Insert

Hi

[quoted text clipped - 38 lines]

David

 

[Ozzie via OfficeKB.com]

Hi Ron,

No, I need to copy the cells from each sheet, and paste them into D1 before
we delete columns A:C

WSNew.Range("A1:C2").Copy WSNew.Range("D1")

I tried adding the paste function by the line of code above, but can't seem
to do it?

Thanks for your help,

Hi Ozzie

Do you want to copy the cells from ws1 to the sheets?

Morning Ron

[quoted text clipped - 17 lines]

 

[Ron de Bruin]

It is working for me

Why do you copy two rows to D1 before you delete Column A:C
WSNew.Range("A1:C2")



--
Regards Ron de Bruin
http://www.rondebruin.nl

Hi Ron,

No, I need to copy the cells from each sheet, and paste them into D1 before
we delete columns A:C

WSNew.Range("A1:C2").Copy WSNew.Range("D1")

I tried adding the paste function by the line of code above, but can't seem
to do it?

Thanks for your help,

Hi Ozzie

Do you want to copy the cells from ws1 to the sheets?

Morning Ron

[quoted text clipped - 17 lines]

David

 

[Ozzie via OfficeKB.com]

Ron,

My mistake, my spreadsheet was out by a few lines which is why it was copying
and pasting blank cells!. I amended this and it now works fine, as you said
it would.

Many thanks for all your help,

Cheers,

David

It is working for me

Why do you copy two rows to D1 before you delete Column A:C
WSNew.Range("A1:C2")

Hi Ron,

[quoted text clipped - 17 lines]