Range and paste problem
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Range and paste problem

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     

    Excel programmers
    I would like to run an Excel VBA code on an excel sheet that contains data that varies from day to day. And copy that range to a sheet that contains rows of the same data.
    How can write the code to specify the destination range to start pasting at the start of an empty row after the exciting data (I hope I am making sense) .it Like Appending the data. Knowing that there is empty rows.. So I would like to start from the last row from the sheet and go up.
    I have started to write the code but couldn’t know how to finish it


    Dim I as Long
    Dim RngDestination As Range
    Dim Wkb As Workbook
    Dim Wks As Worksheet

    Set RngDestination = Wkb("Test for Annuity Daily Adds.xls") .Wks("Annuity Rec Feb 2002").Range("A6522").select
    For i = Cells(Rows.Count, "A").End(xlUp).Row To i Step -1
    Cells(i, "A").EntireRow.Copy Destination:=RngDestination

    Next i
    End Sub

    This code does not work J
    Thanks for the help

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Stono

    This snip of code might help you.
    It selects the cell in column A immediately below the last row with data, (for you to paste into). Perhaps you can adapt it for your purposes.

    Count = 0
    For Each cell In [A65536:IV65536]
    If cell.End(xlUp).Row > Count Then
    Count = cell.End(xlUp).Row
    End If
    Next cell
    Range("A" & Count + 1).Select
    End Sub

    Regards
    Derek

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I worked on it but it over writes the copyed value??
    could you see what am i doing wrong .
    i wanted to see if it works on just one workbook
    Sub COPY()

    Dim I As Long
    Dim cell As Range
    Dim Wkb As Workbook
    Dim Wks As Worksheet
    Dim count As Long

    count = 0
    For Each cell In [A65536:IV65536]

    'Worksheets("sheet1").Range ("A")
    '(A65536:IV65536)
    If cell.End(xlUp).Row > count Then
    count = cell.End(xlUp).Row
    End If

    Range("A" & count + 1).Select



    For I = Worksheets("sheet2").Cells(Rows.count, "A").End(xlUp).Row To I Step -1
    Cells(I, "A").EntireRow.COPY Destination:=Worksheets("sheet1").Range("A" & count + 1)
    Debug.Print count
    Debug.Print I

    Next I
    Next cell
    End Sub


    thanks

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again
    I'm afraid I am a self-taught macro man so my code would probably be laughed at by proper programmers. Anyhow If I wanted to keep copying rows 1:10 from Book5 to Book6 this is the code I would use.
    This goes to the receiving Book6 first to select the cell where you are going to dump, then returns to Book5 to copy the rows needed
    The first dump is on line 2 of Book6, thereafter it dumps on the next free row.


    Application.ScreenUpdating = False
    Windows("Book6").Activate
    Count = 0
    For Each cell In [A65536:IV65536]
    If cell.End(xlUp).Row > Count Then
    Count = cell.End(xlUp).Row
    End If
    Next cell
    Range("A" & Count + 1).Select
    Windows("Book5").Activate
    Rows("1:10").Select
    Selection.Copy
    Windows("Book6").Activate
    ActiveSheet.Paste
    Windows("Book5").Activate
    Application.CutCopyMode = False
    End Sub

    Hope this gives you some ideas
    Regards
    Derek

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      

  6. #6
    Guest

    Default

    Thank you man .. it did work

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com