Copy and paste a range of cells to the last blank row on a sheet - and clear contents

**glock9mm** · 02-02-2013, 07:38 PM

I am trying to write a macro to do the following:

Copy range A1:K12 and paste the entire range to the last blank row within the sheet. Also, clear contents of the following cells within this range (B3,B4,C4,D4,E4,F4,G4,H4,I4,J4,K4,B5,C5,D5,E5,F5,G5,H5,I5,J5,B6,C6,D6,E6,F6,G6,H6,I6,J6,K6,B8,D8,F8,H8,J8,B9,B10,B11,B12)

Any help greatly appreciated!

**jeffreybrown** · 02-02-2013, 07:59 PM

Did you turn on the macro recorder and record those steps?

Do this will get you the meat of the macro and then if you post the macro here we can help you clean it up...

or

...use this to help clean up your recorded macro Beyond Excel’s Macro Recorder

**glock9mm** · 02-02-2013, 08:09 PM

the code is as stated below although in this case A22 was the last blank cell in column A...i want it to be automated to find the last blank cell in column A on this sheet and paste the range accordingly. Also, not sure if 'Application.CutCopyMode = False' is necessary?

Please Login or Register  to view this content.

**glock9mm** · 02-02-2013, 08:15 PM

sorry i meant NEXT empty cell not last empty

**jeffreybrown** · 02-02-2013, 08:18 PM

Not so sure this macro makes sense to me as it clear contents which were just pasted.

Try...

Please Login or Register  to view this content.

**glock9mm** · 02-02-2013, 08:38 PM

should have been more clear..this range is a form that a user would fill out so when copied, i want the contents of the user defined fields(all but cells in column A within range) to cleared so the form is ready to be filled out again. The macro you provided works great and thank you. Just one question, since this is going to be run multiple times is there a way to make the 'Range("B24:K33").ClearContents' dynamic as it got an error on this line the second time running the macro(due to the copied range being below that range every time running the macro after the first time.)

**jeffreybrown** · 02-02-2013, 08:48 PM

Originally Posted by glock9mm

Just one question, since this is going to be run multiple times is there a way to make the 'Range("B24:K33").ClearContents' dynamic as it got an error on this line the second time running the macro(due to the copied range being below that range every time running the macro after the first time.)

Yes you can make it dynamic, but dynamic off of what?

**glock9mm** · 02-02-2013, 08:55 PM

off of where it is pasted. The second time i run this macro the range needed to be cleared would not be B24:K33 because it would be pasted to the next empty cell. So the second time it is ran it would need to clear contents of L24:U33 and so on everytime it is ran.

**jeffreybrown** · 02-02-2013, 09:25 PM

I would use some empty cells to hold the column number so the next time the macro is run the macro knows what columns were previously cleared...

I used J1 and K1 to hold the column number and the next time the macro is run it adds 10 to the previous column numbers...

Please Login or Register  to view this content.

**glock9mm** · 02-02-2013, 10:00 PM

It appears that code works but i think i told you this would be pasted horizontally as opposed to vertically. Let me try to explain...

The range I am using(and works with the first part of your code you sent) is A41:K55

Please Login or Register  to view this content.

When i run the above macro it copies the range (A41:K55) to A56:K70 because A56 is the next empty cell and I would need B58:K70 to be cleared. It would follow this same pattern every time the macro is run.

Is this enough info to tweak the marco to clear contents dynamically?

**jeffreybrown** · 02-02-2013, 10:13 PM

Do you have a sample file you can attach?

If I follow what you just said above...

Copy A41:K55
Paste to A56:K70 (next open cell in column A)

Now you want to...
Clear contents of B58:K70

This overlaps what you just copied and pasted...

I'm not following...Remember, I don't see the same file you are looking at...

**glock9mm** · 02-02-2013, 10:32 PM

example file.xlsm

See attached example file

**jeffreybrown** · 02-03-2013, 10:56 AM

I am completely lost as to what you want...

You talk about copying an area, clearing an area, and moving data to the right...

In your sample file there is no example of before and after.. Please update with a before and after so I can see exactly what you want the macro to do.

**glock9mm** · 02-03-2013, 01:22 PM

example file.xlsm

see attached w/ before and after. I just need the form copied to the next empty cell and all the user defined fields cleared out every time it is copied so the form can be completed by another user.

**jeffreybrown** · 02-03-2013, 01:38 PM

Okay I think this will do it which follows the pattern...

Please Login or Register  to view this content.

**glock9mm** · 02-03-2013, 01:55 PM

perfect! thanks so much for your help and patience

**jeffreybrown** · 02-03-2013, 01:58 PM

You are very welcome, but I must thank you for your patience with me.

Thanks for the feedback

**panton** · 02-10-2013, 05:06 PM

Hi
I have been following this thread with great interest and I'm trying to learn something as well.
JeffreyBrown! If I use your solution in post #15 02-03-2013 at 12:38 but want the copied cells to be pasted in another sheet (sheet3), but otherwise exactly the same parameter. How can that be done?
Regards from Panton

**jeffreybrown** · 02-10-2013, 05:32 PM

Hi Panton & Welcome to the Forum,

Well it seems there may be a little bit more involved here, but in a nut shell, you simple qualify the sheet you need to paste too.

Not only do you need to qualify the sheet, now you will possibly have to find the last row twice.

Once on the source sheet and then on the destination sheet.

I would love to help, but to make sure we get your requirements right, can you create a new thread plus upload a sample workbook with a clear before and after.

You can PM me the new link and I will help you along.

Copy and paste a range of cells to the last blank row on a sheet - and clear contents

LinkBack

Thread Tools

Rate This Thread

Display

Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Re: Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Re: Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Re: Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Re: Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Re: Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Re: Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Re: Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Re: Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Re: Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Re: Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Re: Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Re: Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Re: Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Re: Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Re: Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Re: Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Re: Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Re: Copy and paste a range of cells to the last blank row on a sheet - and clear contents

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions