Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Posts
    1

    Unanswered: Copying and Pasting without overwritting

    Hey all, long time lurker first time poster.

    I'm trying to create a macro that will copy and paste information from one workbook to another, without overwritting information that is already on the page.

    Essentially, what I want to do is copy and paste on the next available line.

    Thus far, I have:

    Sub MacroH()
    '
    ' MacroH Macro
    ' Macro will convert data onto CSV.
    '
    ' Keyboard Shortcut: Ctrl+h
    '
    Range("A1:AC24").Select
    Range("A24").Activate
    Selection.Copy
    Windows("CSV Values.csv").Activate
    NextRow = Sheets("CSV Values").Range("F65536").End(xlUp).Row
    Cells(NextRow, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


    End Sub

    The sheet that I am copying to is also in CSV format. Not sure if this would have any impact on what I am trying to do. Any help would be fannnnnntastic.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You don't say what you problem is but this may do what you want
    Code:
        Dim NextRow As Long
        Range("A1:AC24").Copy
        Windows("CSV Values.csv").Activate
        With Worksheets("CSV Values")
            NextRow = .Range("F65536").End(xlUp).Row + 1
            .Cells(NextRow, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
        End With
    ??

    MTB

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    That should do it. But if you are moving to XL 2007, then you might want to "count" the rows rather than hard code 65536. XL 2007 has 1,000,000 + rows.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    Here's another take. This does not use Copy Paste. You do not have to use copy paste if you are transferring values from one Book Worksheet to another Excel Book / Sheet. This will also be faster not having to access the clipboard.

    Code:
    Sub Trnsfer()
    Dim nRow As Long, nSRow As Long, nSCol As Long
    Dim n As Long
    Dim srcSht As Object
    Dim trgSht As Object
    
        nSRow = 25
        nSCol = 24
        
        Set srcSht = ActiveSheet
        Set trgSht = Workbooks("CSV Values.csv").Sheets("CSV Values")
        
        With trgSht
            nRow = .Range("F65536").End(xlUp).Row - 1
            For n = 2 To nSRow
                nTRow = nRow + n
                .Range(.Cells(nTRow, 1), .Cells(nTRow, nSCol)).Value = srcSht.Range(srcSht.Cells(n, 1), srcSht.Cells(n, nSCol)).Value
            Next
        End With
        trgSht.Activate
    End Sub
    ~

    Bill

Posting Permissions

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