Copy select cells from one sheet into the next available column in another sheet

**gsjan1** · 01-27-2012, 03:43 PM

I have data in several cells that are located in various spots within Sheet1(AMB). I have created a macro to copy these (with a Paste Special, Value function) into Sheet2(TREND) in one continuous column (see below). My question is - How can I write this macro so that each week it will copy this information into the next available column? So, next week's data will be dumped into Column C, then week 3 gets dumped into column D, etc. The cell locations in Worksheet 1 will always be the same and the Rows it gets dumped into in Worksheet 2 will always be the same. I just need the column to increment 1 each time the macro is run. I appreciate any help!

Please Login or Register  to view this content.

**jaslake** · 01-27-2012, 04:55 PM

Hi gsjan1
I've inserted into your code the code to do this

How can I write this macro so that each week it will copy this information into the next available column

I also took the liberty to rewrite the first several lines of your code. The code was obviously recorded which is absolutely fine. Recorded code, however, results in many unnecessary lines that can be edited out. You'll notice in the lines I rewrote, there's no selecting going on.

Try the code then try to rewrite the rest of it without selecting...just be sure to notice the . (dot) within the With...End With statements. This is critical to the success of the code. If you need further help, let me know.

Please Login or Register  to view this content.

**gsjan1** · 01-27-2012, 05:55 PM

Hi Jaslake,

Thank you so much for your kind reply! I just copied and pasted everything and when I run the macro I get the following error: "Ambiguous Name Detected: Trend". I'm trying to figure out why that might be. I am very much a novice with VBA, so maybe it's just something very basic. Thoughts?

Thanks much!

**jaslake** · 01-27-2012, 06:03 PM

Hi gsjan1
This would suggest you have two procedures with the name "Trend". Perhaps you placed the code I posted in your existing module or into a new module but your old code is still out there intact. If this is the case do one of two things...either rename one of the procedures (to Trend1 for example) or comment out ALL the lines of your existing procedure.

Let me know how you make out.

**gsjan1** · 01-27-2012, 06:11 PM

Hi there,

I got past the ambiguous name error - so I'm good there. I'm troubleshooting something else now (it isn't pasting values) - will give you a thumbs up when I've got it working good. Many thanks!

**gsjan1** · 01-27-2012, 06:17 PM

Jaslake - you are GENIUS! It's working!! I still have to rewrite those other lines to neaten it up and play with some formatting issues, but - it IS copying to the next column just like I needed. A big hurdle cleared. Many thanks!

**jaslake** · 01-27-2012, 06:43 PM

Hi gsjan1
Not a genius...far from it. Though there are some members of this Forum that truly are. You've benefited from what I've learned from them. Others will benefit from what you've learned from us.
Glad I could be of help. Good hunting...need further help, let me know.

**gsjan1** · 01-27-2012, 07:31 PM

Hey jaslake,

I do need more help! I'm all the way to the end and I want to format Rows 6:18 as a percentage format. Is there a way I can do this without having to type the "End-With" segment over and over? I'll attach the whole code - but you can go straight to the bottom.

Also - my next challenge would be - how difficult would it be to conditional format the newly copied column by comparing it to the previous column and coloring it red if the number is higher and green if the number is lower? If that can be accomplished, I will have reached macro nirvana!

Thanks for your help!

Please Login or Register  to view this content.

**jaslake** · 01-28-2012, 01:37 AM

Hi gsjan1
Let me look at this in the morning...too late tonight.

**jaslake** · 01-28-2012, 02:01 PM

Hi gsjan1

Try this code for both issues

Please Login or Register  to view this content.

**gsjan1** · 01-30-2012, 06:02 PM

Hi Jaslake,

I will be forever indebted to you for helping to create this awesome tool - It works wonderfully! ...except...one last piece. I have a button on the AMB sheet for the user to activate the macro once they've entered their data. As the code is currently written, it formats the cells on the AMB page instead of the Trend page. I tried inserting further definition into the range, but it doesn't seem to like it as now I get an error stopping on that line (Application Defined or Object Defined error). If you can help me with this last piece of syntax, I'll be off and running. A zillion thanks to you!

Please Login or Register  to view this content.

**gsjan1** · 01-31-2012, 03:33 PM

Hey Jaslake,

I ended up starting another post and got the question answered. Thanks so much for your help. I'm all set now. I wish I could buy you a pop or something.

Peace,
Janet

**jaslake** · 01-31-2012, 03:36 PM

Hi gsjan1
Sorry, with all the Forum issues I failed to get yesterday's notification. Glad you got it resolved.

**gsjan1** · 01-31-2012, 04:18 PM

Hi there,

You're back! And so am I.

I thought the other solution had fixed it - I was in a rush to get to a meeting - but now that I am back and looking at it closely, it actually didn't fix it. Rats! So, I'm right back where I was before. Any suggestions?

**gsjan1** · 01-31-2012, 04:50 PM

Jees - Never mind! I went back and doublechecked the other solution and discovered a bit of "operator error" on my part. I missed a "dot". It DOES in fact work fine now. persnickity little dots...

**jaslake** · 01-31-2012, 05:55 PM

I've had a dot or two destroy my procedures. Great hunting.

Copy select cells from one sheet into the next available column in another sheet

LinkBack

Thread Tools

Rate This Thread

Display

Copy select cells from one sheet into the next available column in another sheet

Re: Copy select cells from one sheet into the next available column in another sheet

Re: Copy select cells from one sheet into the next available column in another sheet

Re: Copy select cells from one sheet into the next available column in another sheet

Re: Copy select cells from one sheet into the next available column in another sheet

Re: Copy select cells from one sheet into the next available column in another sheet

Re: Copy select cells from one sheet into the next available column in another sheet

Re: Copy select cells from one sheet into the next available column in another sheet

Re: Copy select cells from one sheet into the next available column in another sheet

Re: Copy select cells from one sheet into the next available column in another sheet

Re: Copy select cells from one sheet into the next available column in another sheet

Re: Copy select cells from one sheet into the next available column in another sheet

Re: Copy select cells from one sheet into the next available column in another sheet

Re: Copy select cells from one sheet into the next available column in another sheet

Re: Copy select cells from one sheet into the next available column in another sheet

Re: Copy select cells from one sheet into the next available column in another sheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions