Whenever you copy some data from one place to another, by definition you have a source and a destination. In this case, your source is somewhere in ROM and your destination is the PPU (VRAM). Writes to $2006 will set your destination address, and then you write to the destination with sta $2007. The PPU takes care of incrementing the addresses for you, so you don't have to do anything.
The source is where you are having some problems. First, all addresses are 16-bit, which is why we need two variables (1 byte each) to represent one address. The 6502 is little endian, so addresses need to be stored low-byte first (high-byte last). So when we make a pointer variable in RAM, the low byte needs to come first, hence the order:
AddrLow .rs 1
AddrHigh .rs 1
Now as you suspected, the magic is in the brackets []. Putting brackets around a variable Z translates to "the address located at Z and Z+1". So when we have an instruction like this:
lda [AddrLow], y
it translates into English as "Go to the address stored in AddrLow and AddrLow+1. Add y to that address. Read the byte located there and stick it in the Accumulator."
If you look at our variable declarations above, you will see that AddrLow+1 is none other than AddrHigh. So in our case, the instruction translates to "Go to the address stored in AddrLow and AddrHigh. Add y to that address. Read the byte located there and stick it in the Accumulator."
This is the piece you were missing I think. We only put one variable (AddrLow) in the brackets, but the 6502 extrapolates that to two variables (AddrLow and AddrHigh) and makes a 16-bit address out of them.
^^ That's a 32x32 tile.
i.e. 2x2 each, 16x16 tiles.
Top left is using white, dark red, and light red.
Bottom left is blue, dark blue, and white.
Top right is using dark and light red.
Bottom right is using black, light red, dark red, and yellowish.
This is my shiny thing, and if you try to take it off me, I may have to eat you.
Check out my dev blog.