Adding data to next vacant cell

[meddington]

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:

Please Login or Register  to view this content.

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

[boylejob]

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

[mikerickson]

This should help.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

will fill B2 with the value that is in A1

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

[meddington]

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.

[meddington]

Ok, my code now looks like this...

Please Login or Register  to view this content.

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.

[boylejob]

Edd,

This should get you started. Of course you may have to adjust a few things to fit your actual data. This worked on the sample files you provided.

Also, keep in mind that several things are hard coded based on the samples you sent so if you adjust any data locations, you will also need to adjust the code.

Please Login or Register  to view this content.

Hope this helps you get started working with VBA.

[meddington]

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.

Please Login or Register  to view this content.

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?

[boylejob]

Edd,

Don't get discouraged. I doubt that there is a one of us out there that did not go through the same thing. I believe you indicated this was your first attempt at a macro so you may have to work through a few things.

Here are few things to look at and consider.

1. What type of error messages are you getting.

2. Keep in mind that the code I sent you is hard coded in a couple of areas. As an example, the 4 in the code below is look ONLY at column D so if the information is in a different location in the real data, some things are going to have to be adjusted.

Please Login or Register  to view this content.

3. On this piece of code

Please Login or Register  to view this content.

be sure you are putting the file name after workbooks and the tab or sheet name after sheets.

Just keep posting. I'll be more than happy to keep working on this with you and hopefully we will be able to get your problems solved.

[meddington]

We'll, I'm not getting any errors, and I did manage adjust the code for my specific worksheet and data, but when I run the Macro, it places the data starting in column Q for some reason, and subsequent runnings simply paste data on top of the previous data. This is my code now, the only thing changed from the code you sent is the workbook and sheet name and the position of the data to paste...I think...

Please Login or Register  to view this content.

Again, it works as advertised on the sample files, so I can't figure it out.

[boylejob]

Edd,

I've added a few comments to the code which hopefully will make things a little easier to understand. I have also changed lI to lVariable to make it easier to see.

Please Login or Register  to view this content.

Now, lets looks a couple of things in the For Next statement.

1. In your sample file we use lVariable to denote the row and we are telling it to start on row 19 and end on row 34. In the test file we told it to start with row 4 and end with 18.

2. In the end results file we use lVariable to denote the column (IVariable -2). It the original test file lVariable starts out at 4 so 4-2=2 or column B. In your updated code you start with lVariable being 19 thus 19-2=17 or column Q.

Let me ask two questions about your real data

1. In you sample data what rows does the data start and end on.

2. In your end use file what column do you want to start with.

Answers to these questions will determine where your For Next statement needs to begin and end. Remember, some of this is what we call "hard coded" which basically means we are not using variables and the numbers stay the same.

Try pasting the updated code and run it again. Remember pay close attention to were your sample rows begin and end and what column you want to start with on your end user file.

I'm more than happy to keep helping until we get this working for you. Let me know what happens.