Copying and Pasting without overwritting

ImHaiHuynh · #1 (**permalink**) 08-06-07, 15:08

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.

MikeTheBike · #2 (**permalink**) 08-07-07, 08:11

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

shades · #3 (**permalink**) 08-07-07, 16:31

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.

savbill · #4 (**permalink**) 08-07-07, 21:03

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

Internet Services

Web Development

Digital Marketing

Consumer Tech