VBA code does not copy and paste to next row for each zip code
 

Hi, I started to learn VBA in excel and I'm trying to do the following. I
want to copy and paste a table for each 3-digit zip code (006 to 300). In
other words, I want to copy and paste the table for the first zip code (006),
and then copy and paste the same table but using the next 3-digit zip code
(007) right below the previous zip code . My VBA code is below, but it's not
working right. It does copy and paste in the same place over and over for
each zip code instead of going down to the next row and do the same trick.
Here's what I'm trying to do:

006 120
006 140
: :
006 9974
007 120
007 140
: :
007 9974
008 120

and so on

My VBA code is shown below:

Dim i As Integer
For i = 6 To 300


ActiveCell.Range("a:a").Value = i
Range("B1").Select
Sheets("Sheet2").Select
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False


Next i

End Sub


I would appreciate any help.
Thanks

VBA code does not copy and paste to next row for each zip code
 

On Nov 13, 12:34*pm, Miguel wrote:
Hi, I started to learn VBA in excel and I'm trying to do the following. I
want to copy and paste a table for each 3-digit zip code (006 to 300). In
other words, I want to copy and paste the table for the first zip code (006),
and then copy and paste the same table but using the next 3-digit zip code
(007) right below the previous zip code . My VBA code is below, but it's not
working right. It does copy and paste in the same place over and over for
each zip code instead of going down to the next row and do the same trick..
Here's what I'm trying to do:

006 * 120
006 * 140
* : * * * :
006 * 9974
007 * 120
007 * 140
* : * * * :
007 * 9974
008 * 120

and so on

My VBA code is shown below:

Dim i As Integer
* For i = 6 To 300

* * ActiveCell.Range("a:a").Value = i
* * Range("B1").Select
* * Sheets("Sheet2").Select
* * Selection.Copy
* * Sheets("Sheet1").Select
* * ActiveSheet.Paste
* * Range("A1").Select
* * Application.CutCopyMode = False

* Next i

End Sub

I would appreciate any help.
Thanks

Sorry, Miguel, but there's not enough info to go by to understand what
you're trying to do here. We don't have the luxury of knowing what
"zip code tables" you're talking about. For instance, when it changes
to Sheet2 and copies, what is it copying? Is it a range of cells, one
cell, ??? All that code does is set column A on Sheet1 to the value
of "i", copy something unknown from Sheet2, and then pastes it in some
random location on Sheet1.

Thanks! Feel free to email me directly since I don't get out to the
news group very often. I'd be happy to help if I had some more info.

Cory
http://www.hishandsphotographs.com

VBA code does not copy and paste to next row for each zip code
 

Enter your table to copy in Sheet2 Col A
Then run the following macro...
It will create the repeating table for each number from 6 to 300 in Sheet1
Col A
You can format Col A as Custom|000

Sub t()
Dim i, j, k, l As Long
k = Worksheets("sheet2").Range("A65536").End(xlUp).Row
Application.ScreenUpdating = False
For i = 6 To 300
l = (i - 6) * k
For j = 1 To k
Worksheets("Sheet1").Cells(l + j, 1) = i
Worksheets("Sheet1").Cells(l + j, 2) = Worksheets("Sheet2").Cells(j, 1)
Next j
Next i
Application.ScreenUpdating = True
End Sub

VBA code does not copy and paste to next row for each zip code
 

Thanks for the code. It works great.

Miguel

"Sheeloo" wrote:

Enter your table to copy in Sheet2 Col A
Then run the following macro...
It will create the repeating table for each number from 6 to 300 in Sheet1
Col A
You can format Col A as Custom|000

Sub t()
Dim i, j, k, l As Long
k = Worksheets("sheet2").Range("A65536").End(xlUp).Row
Application.ScreenUpdating = False
For i = 6 To 300
l = (i - 6) * k
For j = 1 To k
Worksheets("Sheet1").Cells(l + j, 1) = i
Worksheets("Sheet1").Cells(l + j, 2) = Worksheets("Sheet2").Cells(j, 1)
Next j
Next i
Application.ScreenUpdating = True
End Sub

VBA code does not copy and paste to next row for each zip code
 

I actually have a question. Since I can only do this trick for the first 135
codes due to the limit of number of rows (65536) how can I change the macro
to start zips from 135 to 270 and so on?. I tried by changing "i" but it does
not work.

Thanks
Miguel

"Miguel" wrote:

Thanks for the code. It works great.

Miguel

"Sheeloo" wrote:

Enter your table to copy in Sheet2 Col A
Then run the following macro...
It will create the repeating table for each number from 6 to 300 in Sheet1
Col A
You can format Col A as Custom|000

Sub t()
Dim i, j, k, l As Long
k = Worksheets("sheet2").Range("A65536").End(xlUp).Row
Application.ScreenUpdating = False
For i = 6 To 300
l = (i - 6) * k
For j = 1 To k
Worksheets("Sheet1").Cells(l + j, 1) = i
Worksheets("Sheet1").Cells(l + j, 2) = Worksheets("Sheet2").Cells(j, 1)
Next j
Next i
Application.ScreenUpdating = True
End Sub

VBA code does not copy and paste to next row for each zip code
 

For i = 6 To 300
Change the above to
For i = 135 to 270


Worksheets("Sheet1").Cells(l + j, 1) = i
1 in Cells(l + J,1) implies Column A, you can change it to 3,5,7...

Worksheets("Sheet1").Cells(l + j, 2) = Worksheets("Sheet2").Cells(j, 1)
If you change 1 to 3 in the previous line then change 2 to 4...

You can also change Sheet2 to Sheet3 and so on...
without changing column nos to get the output in another sheet

Basicall copy the code between For i = 6 to 300 and Next i as many time you
want and
1. Change i range
2. Either change the column it is writing to or change the sheet name for
each set



"Miguel" wrote:

I actually have a question. Since I can only do this trick for the first 135
codes due to the limit of number of rows (65536) how can I change the macro
to start zips from 135 to 270 and so on?. I tried by changing "i" but it does
not work.

Thanks
Miguel

"Miguel" wrote:

Thanks for the code. It works great.

Miguel

"Sheeloo" wrote:

Enter your table to copy in Sheet2 Col A
Then run the following macro...
It will create the repeating table for each number from 6 to 300 in Sheet1
Col A
You can format Col A as Custom|000

Sub t()
Dim i, j, k, l As Long
k = Worksheets("sheet2").Range("A65536").End(xlUp).Row
Application.ScreenUpdating = False
For i = 6 To 300
l = (i - 6) * k
For j = 1 To k
Worksheets("Sheet1").Cells(l + j, 1) = i
Worksheets("Sheet1").Cells(l + j, 2) = Worksheets("Sheet2").Cells(j, 1)
Next j
Next i
Application.ScreenUpdating = True
End Sub