+ Reply to Thread
Results 1 to 3 of 3

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

  1. 11-17-2011, 01:48 PM #1
    SDBoca
    SDBoca is offline
    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.
    Reply With QuoteRegister To Reply
  2. 11-17-2011, 01:53 PM #2
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    Please put your code in code tags before the moderators get you. If you get a warning / infraction, no one will be able to help you till you remedy it.
    Reply With QuoteRegister To Reply
  3. 11-18-2011, 11:07 AM #3
    SDBoca
    SDBoca is offline
    Registered User
    Join Date
    11-01-2011
    Location
    Boca Raton, Florida
    MS-Off Ver
    Excel 2003
    Posts
    21

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

    solution:

    Sub insertnameworksheet()

    Const strWSNames As String = "RawDataStore,RawDataDistrict,RawDataRegion,RawDataChain"

    Dim WeekNum As Long
    Dim arrWSName As Variant
    Dim rngUR As Range
    Dim i As Long

    WeekNum = Sheets("RawDataStore").Range("D3").Value
    arrWSName = Split(strWSNames, ",")

    For i = 0 To UBound(arrWSName)
    With Sheets.Add
    .Name = arrWSName(i) & "Wk" & WeekNum
    Set rngUR = Sheets(arrWSName(i)).UsedRange
    .Range("A1").Resize(rngUR.Rows.Count, rngUR.Columns.Count).Value = rngUR.Value
    End With
    Next i

    End Sub
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1