Results 1 to 3 of 3

More efficient script for insert new worksheets, copy and paste values?

Threaded View

  1. #1
    Registered User
    Join Date
    11-01-2011
    Location
    Boca Raton, Florida
    MS-Off Ver
    Excel 2003
    Posts
    21

    More efficient script for insert new worksheets, copy and paste values?

    I created a macro which will insert four new worksheets. Then it will select one worksheet, say RawDataStore, select the cells, then do a paste special into the new worksheet RawDataStoreWkxx. It will repeat this for for District, Region and Chain worksheets. It works but the macro code looks like it could be streamlined. Any suggestions?
    Thanks,

    Sharon

    sub insertnameworksheet()
    '
    ' insert and name new worksheets and copy/paste data into new worksheets macro

    Dim WeekNum As Long
    Sheets("RawDataStore").Select
    WeekNum = Range("D3")
    Sheets.Add.Name = "RawDataStoreWk" & WeekNum
    Sheets.Add.Name = "RawDataDistrictWk" & WeekNum
    Sheets.Add.Name = "RawDataRegionWk" & WeekNum
    Sheets.Add.Name = "RawDataChainWK" & WeekNum

    ‘copy/paste store data to new week
    Sheets("RawDataStore").Select
    Cells.Select
    Selection.Copy
    Sheets("RawDataStoreWk” &WeekNum).Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("A1").Select
    Sheets("RawDataStore").Select
    Range("A1").Select
    Application.CutCopyMode = False
    ‘copy/paste district data to new week
    Sheets("RawDataDistrict").Select
    Cells.Select
    Selection.Copy
    Sheets("RawDataDistrictWk” &WeekNum).Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("A1").Select
    Sheets("RawDataDistrict").Select
    Range("A1").Select
    Application.CutCopyMode = False
    ‘copy/paste region data to new week
    Sheets("RawDataRegion").Select
    Cells.Select
    Selection.Copy
    Sheets("RawDataRegionWk” &WeekNum).Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("A1").Select
    Sheets("RawDataRegion").Select
    Range("A1").Select
    Application.CutCopyMode = False
    ‘copy/paste chain data to new week
    Sheets("RawDataRegion").Select
    Cells.Select
    Selection.Copy
    Sheets("RawDataRegionWk” &WeekNum).Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("A1").Select
    Sheets("RawDataRegion").Select
    Range("A1").Select
    Application.CutCopyMode = False
    End Sub
    Last edited by SDBoca; 11-18-2011 at 11:07 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1