+ Reply to Thread
Results 1 to 2 of 2

Macro Code Only Pastes Once Need to repeat

Hybrid View

  1. #1
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    Fl
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    Macro Code Only Pastes Once Need to repeat

    Hello,

    I Have a couple problems with this Macro code:
    1.
    I have 2 sheets (Data) & (Store) When I run the macro it will create a new sheet(wks) and Copy information from (Data) sheet cells A1 and Pastes to my (store) sheet However It will only paste the information from cell A1 I want the script to keep coping and pasting and stop when it hits a blank cell.
    Here is what I have so far...

    2. Problems with this line of code below: "Sheets("Store").Range("A1").PasteSpecial Paste:=xlPasteValues"
    When I change the name of the sheet from "Store" to "wks" the script will not run correcly however I thought the script already knew that the new sheet was named "wks"

    Can someone help explain some?


        Dim cell    As Range
        For Each cell In Worksheets("data").Range("A1", Range("A1").End(xlDown))
        Dim wks As Worksheet
        Set wks = Worksheets.Add
        Sheets("Data").Select
        Range("A1").Select
        Selection.Copy
        Sheets("store").Select
        Range("a1").Select
        Sheets("Store").Range("A1").PasteSpecial Paste:=xlPasteValues
        wks.Select
        Application.DisplayAlerts = False
        wks.Delete
        Application.DisplayAlerts = True
        Sheets("Data").Select
        Range("A1").Select
        Next cell
    Thank You for any help...

    PS Thanks shg for all your help with my last project...

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    There is no need to Select anything, nor to Loop in this code. I'm also not sure why you are adding a worksheet. If I follow your code correctly this should do what you want
        Dim rCopy  As Range
        Dim wks    As Worksheet
        Set rCopy = Worksheets("data").Range("A1", Range("A1").End(xlDown))
        Set wks = Worksheets.Add 'WHY??
        rCopy.Copy Sheets("store").Range("a1")
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1