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

[Miguel]

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

 

[crferguson]

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

 

[Sheeloo]

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

 

[Miguel]

Thanks for the code. It works great.

Miguel

 

[Miguel]

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

 

[Sheeloo]

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

1 in Cells(l + J,1) implies Column A, you can change it to 3,5,7...
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