Pasting a range of data in another worksheets last row.

[Brad4444]

Please Login or Register  to view this content.

I am trying to copy a range of data from one sheet to another sheets last row. This would allow me to build a long list of data that is dependent on several inputs not shown here. However, I can't seem to figure out the how to get the correct range. I am able to find the last row of the sheet. I then tried to make the range correctly. Finally I pastespecial the data but the compiler says my range is wrong. Anyone know what I can do here?

[Wanting_To_Learn]

Brad,

It may work, but it may not, have you tried not selecting a range to paste in? As in pick the cell the paste is to begin in, and let excel do its thing?
Stayci

[Brad4444]

Please Login or Register  to view this content.

I made the range the next cell that is empty. Now I am getting an "Application-defined or object-define error."

[Wanting_To_Learn]

Please Login or Register  to view this content.

Does this do what you want?

[Brad4444]

It probably does but I was trying to not use Selection and .Select. I might not have a choice soon though because this project is due in a few hours. Thanks for your help though. I appreciate it.

[jaslake]

Hi Brad4444

In this line of code

Please Login or Register  to view this content.

your range includes only a row number, no column letter.

In this line of code

Please Login or Register  to view this content.

your range has a range of A1:F1 and another row.

Don't know what you're trying to do but perhaps this syntax will help push you in the right direction

Please Login or Register  to view this content.

[Brad4444]

Your code is close to what I need John but not exactly.

Please Login or Register  to view this content.

I have two worksheets pretend. One worksheet has some information based on formulas that go to many other locations for data. (Basically filling in a form automated.) The second worksheet is just a long list of form after form one right after another starting in ranges A1-F1 and going down all the way to some unknown row.

My problem is I need to copy the form from the first worksheet and PasteSpecial into the second form. at the end of this huge list. I don't know where I have to paste it so first I have to find the last column and then paste in right under that spot.

The second problem is I don't know if this is possible with out using Selection and .Select in my code. That is what I am trying to figure out right here.

So my final question is...

Please Login or Register  to view this content.

This code appears to take two different rows and paste there which is not what I want.

I want something more like this...

Please Login or Register  to view this content.

Assuming adding the minus 61 moves my range downwards. My big issue is I don't fully understand all the syntax so I can't implement this yet. Which after todays ordeal trying to get through several problems like this has convinced me to get a book later tonight. Not that this forum isn't great. I plan on coming back but I just work better with a book. Makes me old I guess.

Anyways thanks for your guys patience.

[Brad4444]

Syntactically something is wrong with the last two lines here

Please Login or Register  to view this content.

I get a "Run-time error '1004': Application-defined or object-defined error."

EDIT: Actually the compiler says this line has the error 1004.

Please Login or Register  to view this content.

This is the full code.

Please Login or Register  to view this content.

I wonder if the problem has to do with me declaring LastRow as an Integer or if my syntax is tripping me up.

Any thoughts on this are appreciated.

[jaslake]

Hi Brad4444
This code will copy all the data from Sheet 1, Range A2 to F and the last row; it'll paste that data into Sheet 2, Column A in the next available row.

That sounds about what you're trying to do.

Please Login or Register  to view this content.

[jaslake]

Hi Brad4444

I just remembered you said Sheet 1 had formulas...this code copies the VALUES to sheet 2

Please Login or Register  to view this content.

[Brad4444]

John,

I appreciate your help a lot. The code you showed me still gives me issues though.

Please Login or Register  to view this content.

LR2 = Sheet2.Range..... says "object required" and I can't figure out why.

EDIT: Seems to have worked correctly. Final code below.

Please Login or Register  to view this content.

I had to add the Sheets("") around my sheets.

EDIT 2: I spoke too soon. Appears there is a bug somewhere so I will have to investigate. Right now I will say this thread is SOLVED but I might be back. Not sure if the bug is coming from the new code or something else.

[jaslake]

Hi Brad4444

You don't have a line of code

LR2 = Sheet2.Range..... says "object required" and I can't figure out why.

I don't write code in shorthand so I can't tell you if your code is syntactically correct or not. I "almost always" fully qualify my objects like this

Please Login or Register  to view this content.

Not tested as I don't have your worksheet layout to test on.

[Brad4444]

Thanks John. I figured it out before you came back by a minute or two. I might be back in a while if I can't figure out an issue that appears to have popped up.

[jaslake]

Hi Brad4444
If you're not familiar with Sheet Code Names (that's what Sheet1 and Sheet2 are in the code I provided), You might want to take a look at this link. http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

[Brad4444]

Ahhh, I understand better now. I thought you were just giving me an example to go by. In my explanation I just picked arbitrary names to explain the process I was trying to work through.

I took your code and converted the names without adding (" ") to them which caused me some issues.

Also I'd give you more reputation but apparently I have to give some reputation to others before I give more to you.

I don't know if you have the power to do this but I'd suggest changing the reputation to a system like Dreamincode.net. http://www.dreamincode.net/

They have the option to +1 people if you think the advice is good.


Finally, thanks for all your help. My code is way cleaner (50 lines down to 15 per sub.) This makes it much easier to update and read so I am glad for that. I look forward to working with you again in the future. Have a nice day.


EDIT: Is it cold in mineral city? Its 70 here in Cincinnati. :D

[jaslake]

Hi Brad4444

Ha! Gotta laugh at this

I don't know if you have the power to do this

No power here...just a user of the Forum, like you...perhaps with a bit more eperience...but probably not much more.

Glad it works for you...happy to help.

PS: It's about 60 degrees here. I have Children and Grandchildren in Cinci.