Macro to copy/paste info in different sheets and to delete last entry

**barcas84** · 01-17-2012, 05:14 PM

Hello, I need a macro that will do the following:

Copy from Sheet "DataEntry" C22 and paste it in Sheet "Calculations1" (E29)
Copy from Sheet "DataEntry" E22 and paste it in Sheet "Calculations1" (G29)
Copy from Sheet "DataEntry" C25 and paste it in Sheet "Calculations1" (E33)
Copy from Sheet "DataEntry" E25 and paste it in Sheet "Calculations1" (G33)
Copy from Sheet "DataEntry" G24 and paste it in Sheet "Calculations1" (E31)

Every time the macro is executed I will do the same operation but to the next sheet called: "Calculations2", then "Calculations3"...and like that until "Calculations50" once it reaches Calculations50, if the macro is executed again it will display a window message like: "You can't enter more data"

Also I will need a macro to delete the last entry of the previous macro, so that every time you execute it it will delete the last entry...and you can do it as many times as you want until you've deleted all of them.

Thank you!

**JBeaucaire** · 01-17-2012, 08:50 PM

These macros should work together to accomplish that.

Please Login or Register  to view this content.

**barcas84** · 01-19-2012, 10:48 AM

Hi! I'm a little confused... What do you mean by work together...? You mean put them in the same module? How do I execute them? Like if I wanna execute Deletesequential, do I have to execute also any of the others? Also to execute Copysequential do I have to execute SetCount...? Can you help me?

Thank you!!!

**JBeaucaire** · 01-19-2012, 11:15 AM

The macros go into a standard code module. Then from your worksheet, pressing Alt-F8 presents your list of macros, you can run the CopySequential macro to copy to the next sheet (1-50), or run the DeleteSequential to delete the info from the last sheet with data. Both of those macros make use of the SetCount macro when needed. (read the macros you'll see)

You should probably make the SetCount macro private so it does not display in the macro list when displayed.

Please Login or Register  to view this content.

**barcas84** · 01-19-2012, 12:13 PM

Hi! Got it! I tried but is giving an error in the CopySequential in this line:

Please Login or Register  to view this content.

Says its out of range... Any ideas why?

Thanks!!!

**JBeaucaire** · 01-19-2012, 04:00 PM

Sure, "out of range" means the current sheetname can't be found. Calculations20, for instance. The logic of the code seems correct to me, I'd have to have your workbook to test it. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook if you want me to.

**barcas84** · 01-19-2012, 04:14 PM

Sure, here it goes!!!!!! Thank you!

**barcas84** · 01-24-2012, 12:21 PM

Any news on this???

Thanks!!

**barcas84** · 01-24-2012, 12:23 PM

Any news on this???

Thanks!!

**barcas84** · 01-31-2012, 12:21 PM

Bump no response

**JBeaucaire** · 01-31-2012, 12:43 PM

Looks like a typo in the first macro... fix this line:

Please Login or Register  to view this content.

**barcas84** · 01-31-2012, 01:36 PM

HI, thanks for the reply!!! I was having another error, and it was because the DataEntry tab was actually called Data Entry (with a space) so I fixed it, and also the line you told me. Now, when I click the button, it doesn't come up with an error but it opens the Visual Basic and highlights the line of code in the macro:

Please Login or Register  to view this content.

Then I run it there manually without changing anything and it works... Why wouldn't it work in the first place with the button...?

Also there is something that I didn't mention but that the macro needs to do.... The problem is that if I use it, let's say 10 times, it works until Calculations10, then if I reset everything and I want to start from the beginning, the macro keeps going when I left it, in Calculations10...and it won't go to Calculations1 until I run DeleteSequential 10 times.... So my question is, can you make a change so the macro will look the first available Calculationstab? Then if it has info it will jump to the next one... This way, when I reset everything, the macro will always start from Calculations1...

Thank you!

**JBeaucaire** · 01-31-2012, 01:50 PM

The macro currently works in reverse, from last to most recent looking for the next empty sheet. You're saying you want to chaotically clear sheets from anywhere in the list and reuse them? Ouch.

Also, yes, running the DeleteSequential multiple times would do what it was designed to do, free up one sheet at a time.

An all purpose "reset everything" macro might look like this:

Please Login or Register  to view this content.

**barcas84** · 01-31-2012, 02:07 PM

Thanks for the Reset Macro! I'm currently using this one:

Please Login or Register  to view this content.

About the change in the process...yeah I'm sorry I forgot to mention it earlier... But basically the macro would have to go to Calculations1 in first place, and then jump to the next if Calculations1 has data already...and like that until it finds one empty, then it would paste the data there... The DeleteSequence should be the same.... I have one macro that does something like that but jumping 2 rows within the same sheet instead of jumping sheets...maybe it helps!

Please Login or Register  to view this content.

**JBeaucaire** · 01-31-2012, 07:57 PM

The maybe just reverse the order the SetCount macro checks sheets:

Please Login or Register  to view this content.

**barcas84** · 02-01-2012, 09:50 AM

PERFECT!!!!! Now it works beautifully!!! Thanks a lot for your help!

**JBeaucaire** · 02-01-2012, 11:07 AM

Wait, are you sure? Looking at that again I think it would keep updating Calc2.... is that what it's doing for you and you didn't notice?

**JBeaucaire** · 02-01-2012, 11:17 AM

I think the SETCOUNT thing doesn't work any more, when you DELETE you always want to go from last sheet to first, but when you COPY new data you are now searching from first sheet to last looking for an empty one. So, I think the macros all need to change. Use these instead:

Please Login or Register  to view this content.

**barcas84** · 02-01-2012, 11:40 AM

You are right!!! It wasn't working correctly... Now it is! But I have one more question... This workbook, when you enter the info it does the macro that you've written and also this one here:

Please Login or Register  to view this content.

Which copies the info in a list... Now my question is, how do I also delete the last record entered in this macro? Something like the DeleteSequential that you wrote but for this macro...

Thanks!!!!

**JBeaucaire** · 02-01-2012, 03:04 PM

These two macros would replace your one CodeList macro:

Please Login or Register  to view this content.

**barcas84** · 02-01-2012, 04:57 PM

AddCodes gives an error, it says: " No cells were found"

**barcas84** · 02-01-2012, 05:42 PM

Also, how could I just clear the cells from Sheet ("PDF"), cells from B26 to B75?

**JBeaucaire** · 02-02-2012, 11:13 AM

1) I edited the AddCodes macro above, try that.

2)

Please Login or Register  to view this content.

**barcas84** · 02-02-2012, 11:59 AM

Hi!, The Addcodes macro was having an error because there are to End If, but only one If so i deleted the second one leaving it like this:

Please Login or Register  to view this content.

And it doesn't give an error but it doesn't work either... I also tried deleting the other one, and same thing...

**JBeaucaire** · 02-02-2012, 02:17 PM

The code above is corrected. Try it again.

**barcas84** · 02-02-2012, 02:30 PM

Now everything works great! Thank you!!!!

Macro to copy/paste info in different sheets and to delete last entry

LinkBack

Thread Tools

Rate This Thread

Display

Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Re: Macro to copy/paste info in different sheets and to delete last entry

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions