|re: I understand how to record a macro (what to click) but don't know how to identify mistakes when my recording doesn't do what I intended."|
As you probably realize, the macro recorder creates very specific code which will basically do nothing more than repeat the steps you took while creating it. To make matters worse, the code is often bloated and inefficient. It just about always need to be cleaned and modified.
One place to start is by reviewing the steps detailed in this tutorial:
These debugging techniques will not only help you "identify mistakes" but it can also help you learn how to write VBA code. By using the debugging techniques discussed, you can reverse engineer code that you find in forums such as these as means to understand how the code does what it does.
re: "How do I create a macro that copies information from one cell in spreadsheet one 21 times down a column in spreadhseet two before moving onto the next cell in spreadsheet one?"
Without the specifics related to your spreadsheet layout, the best we can offer is a generic solution. Perhaps the following code will help you get started, especially if you use the debugging techniques from the tutorial to help you follow along as the code does it's thing.
Let's say you start with this in Sheet1:
This code will place Tom in A1:A21 of Sheet2, then place Bob in A22:A42, etc.
'Determine last Row with data in Sheet 1 Column A
lastRw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Intialize Sheet 2 row counter
rw2 = 1
'Loop through Sheet1!A1:A(lastRw)
For rw1 = 1 To lastRw
'Loop to copy Sheet1 data 21 times
For nxtRw = rw2 To rw2 + 20
Sheets(2).Range("A" & nxtRw) = Sheets(1).Range("A" & rw1)
'Increment Sheet 2 row counter
rw2 = rw2 + 21
You will note my extensive use of comments. That is a great habit to get into. I didn't include the comments just to help you understand the code, I included them so that the next time *I* look at the code, I'll know what I was trying to do.
I have written hundreds of macros for myself and for other people. Without the extensive use of comments, I'd never be able to remember what all of the macros are doing.
A very wise programmer once said:
"Code Tells You How, Comments Tell You Why"
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.