Loop command macro to copy data between worksheets

**ThunderBuck** · 08-11-2009, 09:44 AM

I am having trouble creating a macro for an excel file I am working on. I do not have much experience writing macros, so I apologize for any confusion. I have created a simplified version of the file I am attempting to write a macro for and attached it to my post here.

At this point it would be helpful for you to open up the attached file so that you can understand my explanation.

Basically what I want the macro to do is:
1. Match up "Product" on Wksht(Input) with "Product" on Wksht(Data).
2. Copy the "Usage per ton" and "Dollars per ton" values corresponding to the given product from Wksht(Input) to Wksht(Data) for the correct month. (The user will manually enter the month, ie "August 09" into Wksht(Input) each month when he runs the macro.)
++The color fills will not exist on my finished macro. I only put them there to illustrate the values that will be copied and pasted.

I want the macro to be written with Loop commands so that it will check to be sure the product number matches with the given location for pasting the data values. I should also note I use PasteSpecial_Values because of the formulas in the cells on Wksht(Input).

I would appreciate any suggestions for this situation. Please let me know if you have any questions about my above explanation, I would be glad to offer more details.

**royUK** · 08-11-2009, 09:47 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**ThunderBuck** · 08-11-2009, 09:53 AM

Sorry about the original title. Hopefully this one is specific enough for my problem.

**JBeaucaire** · 08-11-2009, 11:44 AM

Try this:

Please Login or Register  to view this content.

**ThunderBuck** · 08-11-2009, 12:06 PM

JBeaucaire, thanks for your help. That macro definitely serves the purpose I'm looking for in the condensed version I posted on here. I will let you know how it works in my original version after I modify the code for that document.

Again, thanks for your help!

**JBeaucaire** · 08-11-2009, 12:53 PM

if the number of products is not "set" and the range down column A is "varying", then this might help:

Please Login or Register  to view this content.

**ThunderBuck** · 08-11-2009, 03:00 PM

The macro works great, but I have run into one slight problem that I cannot seem to resolve. I modified your code to adapt it to my specific file.

Please Login or Register  to view this content.

The problem I am experiencing is that it copies the data perfectly with the exception of COST for Product A and USAGE for Product B.

Any idea of why this would occur? Did I make any errors you can see in my adaptation of your code?

Thank you so much for your help!

**JBeaucaire** · 08-11-2009, 03:50 PM

It looks fine. Post up your troublesome workbook and let's have a look.

**ThunderBuck** · 08-11-2009, 04:51 PM

Okay, here is my troubled excel file. When I run the macro entitled "DataParse", it performs correctly with the exception of the first two cell entries in the "Data" sheet. I suspect my problem is related to my altering of the definition of "c". I tried Range("1:1") but excel gives me an error when I attempt that.

Side note: You will notice everything beyond the "Alloys" section is recording blank. That is simply because I have not completed my "Input" page. I want to get the macro written before I work through the more tedious portions.

-----

I cannot seem to shrink the file enough to be able to post it on the forum. Any suggestions as to a way around the file limits? I tried compressing it into a .zip file, but received an error message when attempting to upload it.

**JBeaucaire** · 08-11-2009, 05:13 PM

In the code:

Please Login or Register  to view this content.

...simply means "look in row 1 only". Your change means "look in row 2 only". I'm sure that is fine.

**ThunderBuck** · 08-12-2009, 11:01 AM

JBeaucaire,

Thank you for your help. The new problem I was experiencing was a result of the geometrical setup of my workbook. I had a couple locations with "blanks" for the Product, and the macro was storing "zero" values for those locations and essentially over-writing the already copied/pasted data. Stupid error on my part. But again, thanks for your help!

**JBeaucaire** · 08-12-2009, 11:28 AM

Go team! Glad to help.

**ThunderBuck** · 08-12-2009, 11:55 AM

Quick somewhat related question for you, if I want to reference a range:

A
B
C
D
E
XXX
L
M
N
O

Like the one above so that the range is always A through the cell above "XXX" (in this case "E"), what is the most efficient way to do this? Is there a way to adapt the End(xlUp) code to perform in such a way?

I want to build in the ability to insert rows above and below "XXX" without having to alter the macro.

Loop command macro to copy data between worksheets

LinkBack

Thread Tools

Rate This Thread

Display

Loop command macro to copy data between worksheets

Re: Help with writing a specific macro

Re: Loop command macro to copy data between worksheets

Re: Loop command macro to copy data between worksheets

Re: Loop command macro to copy data between worksheets

Re: Loop command macro to copy data between worksheets

Re: Loop command macro to copy data between worksheets

Re: Loop command macro to copy data between worksheets

Re: Loop command macro to copy data between worksheets

Re: Loop command macro to copy data between worksheets

Re: Loop command macro to copy data between worksheets

Re: Loop command macro to copy data between worksheets

Re: Loop command macro to copy data between worksheets

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions