Adding data to next vacant cell

I'm attempting to create a macro (1st time) that will copy data from one worksheet and paste it into another. The problem I have is that I need to run this multiple times so the pasting event needs to occur on a new row each time. As it is, I can only paste new data over existing data, so I need code to find the next empty row. Here's what I have so far, which obviously doesn't work:

Range("C19:C34").Select
    Selection.Copy
    ChDir "ProdMac14:Users:meddington:Desktop:IPA_ExcelFiles:"
    Workbooks.Open FileName:= _
        "ProdMac14:Users:meddington:Desktop:IPA_ExcelFiles:IPA End-User Results - All.xls"
    
        'find first empty row in database

    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
End Sub

my next issue is that this macro will only operate to open the target worksheet to paste into, but I'd like it to activate if the worksheet happens to already be open.

Any help appreciated,

edd

Edd,

I don't believe you have a difficult problem, but we are going to need some more information. We really need to see what your data looks like on your original sheet and what exactly you want to be copied over the new sheet.

Can you post some sample data for us to work with.

Jeff

Ok, two sample files are attached. I want to take the data in column order from the WS_IPA_Sample1/xls and paste it into the next available row (pasting with transpose so the data flows correctly) in the IPA_EndUserResults_All.xls worksheet.

Ok, my code now looks like this...

   Range("C19:C34").Select
    Selection.Copy
    ChDir "ProdMac14:Users:meddington:Desktop:IPA_ExcelFiles:"
    Workbooks.Open FileName:= _
        "ProdMac14:Users:meddington:Desktop:IPA_ExcelFiles:IPA End-User Results - All.xls"
Dim firstBlankCell As Range
Dim firstBlankRowNumber As Long
With Sheets("sheet1")
    Set firstBlankCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
    firstBlankRowNumber = firstBlankCell.Row
End With

    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
End Sub

this will paste the text in the second worksheet on the cell that happens to be active. Am I applying this correctly or is it possible to have the macro look for the first empty cell below the data, rather than the active cell?

As far as the selct/copy/paste code...I suppose I'll adress this, but I'd like to iron out the above first.

Well that works well for the sample files I sent you, yes, but now I'm begining to see how clueless I really am here. I cannot seem to edit the macro to work on my actual files rather than the sample ones I attached earlier. I thought I could just change the code to indicate the correct file name.

Set wsSample = Workbooks("WS_IPA_SAMPLE1.XLS").Sheets("Sheet1")
Set wsEndResults = Workbooks("IPA End-User Results - All.XLS").Sheets("Sheet1")

so instead of WS_IPA_Sample1.XLS, I could replace this with my actual file name, but this doesn't work out quite that easily?

This should help.

Dim firstBlankCell As Range
Dim firstBlankRowNumber As Long
With Sheets("sheet1")
    Set firstBlankCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
    firstBlankRowNumber = firstBlankCell.Row
End With

By the way, you don't need to select/copy and paste to get values from one cell to another

Range("B2").Value = Range("A1").Value

will fill B2 with the value that is in A1

Range("B2").FormulaR1C1 = Range("A1").FormulaR1C1

will fill B2 with the formula found in A1. If there is no formula in A1, then it moves the value.
Even if you are using A1 style references, you need the .FormulaR1C1 property if you want the formulas addresses to be ajusted for relative references.

Range("B2").Formula = Range("A1").Formula

will convert all references to absolute. (well, not quite, the references will remain relative, but they won't be ajusted to their new position.)