Copying loop

**degreez** · 04-22-2010, 04:43 PM

Hi everyone,

Im looking for a VBA code to copy data from A1 (worksheet 1) into $B$8 (worksheet 2) and then do a printout of "worksheet 2". Then reapeat the same for A2...A3...A4, all the way down to A100. I would also like that macro not to do printout if A is blank.

Thanks for your help.

**broro183** · 04-22-2010, 07:03 PM

hi,

Welcome to the Forum

Here is an untested example (without error handling). If you are familar with macros this should point you in the right direction. If you are unfamilar with macros, let us know & someone can provide more details.

Please Login or Register  to view this content.

hth
Rob

**degreez** · 04-23-2010, 04:01 AM

Thanks for quick answer. I'm not really that much familar with macros. I'll test it today when i put my hands on the spreadsheet @ work

I'm just concerned with that line:

.Range("$B$8").Value2 = RngArr(i, 1)

Is it not going from A1 to B1...C1 etc. instead of A1...A2...A3?
Mind me if I'm wrong, as i said im not really familar with VBA, its just my logical thinking

**degreez** · 04-23-2010, 06:34 AM

It's working perfectly. THX a LOT!

**broro183** · 04-23-2010, 06:32 PM

hi,
Thanks for the feedback

Can you please mark the thread as solved?

Originally Posted by degreez

...I'm just concerned with that line:

Please Login or Register  to view this content.

Is it not going from A1 to B1...C1 etc. instead of A1...A2...A3?
Mind me if I'm wrong, as i said im not really familar with VBA, its just my logical thinking

Hey, it's good to be a logical thinker & to ask questions. If you enjoy Excel & want to learn, read a few of the threads & soak up the tips & tricks and you'll be familar with VBA in no time!

I should have commented my code more thoroughly to try and explain what was going on.
To make the macro more efficient I pulled the entire range into VBA as an array* and then iterated through the array (ie the ... "= RngArr(i,1)"), rather than getting info from the sheet cell by cell.

* I may have my terminology slightly wrong & someone with more knowledge may say that the code could be even more efficient using a different type (non-variant typed) array, but I've had some problems in the past so I stuck with what I know.
Anyway... on second thoughts, the Excel VBE Help files (search for "array function" & " using arrays") & the below will be more helpful than my rambling!

Originally Posted by broro183

...Here are some general links/examples:
http://www.excelforum.com/excel-prog...ml#post2289689
http://www.excelforum.com/2289718-post139.html & Shg's example later in the thread: http://www.excelforum.com/excel-prog...ml#post2289718
- In fact, there are good comments right throughout the thread.

hth
Rob