Help with some VBA code to copy and paste on new worksheets

[gwatcheater]

I am trying to write a macro to copy data from one sheet to individual
worksheets

The data is stock prices

Each stock has 2 columns worth: Date and Price

I wish to:
Copy the template tab to create a duplicate worksheet
copy column A and column B data
Paste into column A and B of the new duplicate worksheet
Name the worksheet with the data in Row1

and then do the same for the next set of data - in Column D and E

and then again for the next data in G and H

...etc potentially around 600 stocks

sample data attached



many thanks
Rob


+-------------------------------------------------------------------+
|Filename: sample data.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=110|
+-------------------------------------------------------------------+

 

[joel]

Are you ROB? The code I wrote for the next posting uses the same code.

 

[gwatcheater]

thank Joel - just what I am after...

can I ask for one small tweak

Can I use a sheet called "Template" and copy this each time - pasting
in the data (rather than creating a new blank worksheet?!) still using
the contents of row 1 as the sheet label

 

[joel]

from
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
to
Sheets("Template").copy after:=Sheets(Sheets.Count)
Set NewSht = Activesheet

 

[gwatcheater]

fantastic.... THANK YOU VERY MUCH!!!!!!