Really need some help - Creating a table without manually typing in1000's of cells

[Johndagolfer]

Hi,

I am stumped. I am not good at writing macros(but I am learning).
Here is my situation. I have 2 tables. One table is a summary, while
table 2 shows the specifics of the items in table one.

Once someone types an ID # into table one it immediately populates
table 2 with 18 rows (that all have the exact same ID #).

If someone creates another entry another 18 rows are populated.

What I am trying to do is on the 2nd worksheet link the top cell and
pull down 17 more cells(then vlookup the figures). The problem is if
I have 500 entries, i have to create the top cell and pull down 17
cells 500!!! times.

Is there an easy way to do this?

Thanks,

John

 

[Joel]

It is not clear what you mean by pulling down 17 more cells. Also, where are
the ID numbers coming from. It may help if you post you old code.

 

[Johndagolfer]

It is not clear what you mean by pulling down 17 more cells. Also, where are
the ID numbers coming from. It may help if you post you old code.

I actually haven't written any code. I am unsure how to proceed is
the main problem


so let me explain

on the first sheet it looks like this


ID Data1 Data2 Data3
Data4.....................................

111 # # #
#............................................


The second sheet needs to be in this format

ID

111 Data1 #
111 Data2 #
111 Data3 #
111 Data4 #
111 Data5 #
112
112
112
....
....
...
...
113
113
113.


So as data in input horizontally it's creating on the 2nd sheet
individual data(basically in one long column).
When the next row of data is input on the original sheet it creates
the exact same number of data points below the original input.

Any ideas?

 

[Joel]

I hard coded the numbers on sheet 2, didn't use an links. I can modify the
code to add formulas if you need it.

Sub MoveData()

RowCount = 1
LastRow = 1
With Sheets("Sheet1")
Do While .Range("A" & RowCount) <> ""
ID = .Range("A" & RowCount)
For ColCount = 2 To 19
Data = .Cells(RowCount, ColCount)
With Sheets("Sheet2")
.Range("A" & LastRow) = ID
.Range("B" & LastRow) = Data
LastRow = LastRow + 1
End With
Next ColCount
RowCount = RowCount + 1
Loop
End With
End Sub

 

[Johndagolfer]

I hard coded the numbers on sheet 2, didn't use an links.  I can modifythe
code to add formulas if you need it.

Sub MoveData()

RowCount = 1
LastRow = 1
With Sheets("Sheet1")
   Do While .Range("A" & RowCount) <> ""
      ID = .Range("A" & RowCount)
      For ColCount = 2 To 19
         Data = .Cells(RowCount, ColCount)
         With Sheets("Sheet2")
            .Range("A" & LastRow) = ID
            .Range("B" & LastRow) = Data
            LastRow = LastRow + 1
         End With
      Next ColCount
      RowCount = RowCount + 1
   Loop
End With
End Sub













- Show quoted text -

I've edited the code so that colums 18 to 33 are in the data fields.
But I can't figure out how do make it so that columns 34 to 53 will
display in the next column.

This is exactly what I need my data to look like.

ID SEQUENCE MONTH_YEAR PROJECTED_SAVINGS ACTUAL_SAVINGS QUARTER
FISCAL_YEAR
1 1 8/1/2008 1200 1200 1 2009
1 2 9/1/2008 1500 1500 1 2009
1 3 10/1/2008 500 500 2 2009
1 4 11/1/2008 2100 2100 2 2009
1 5 12/1/2008 0 0 2 2009
1 6 1/1/2009 0 0 3 2009
1 7 2/1/2009 0 0 3 2009
1 8 3/1/2009 1600 1600 3 2009
1 9 4/1/2009 2000 2000 4 2009
1 10 5/1/2009 0 0 4 2009
1 11 6/1/2009 0 0 4 2009
1 12 7/1/2009 0 0 1 2010
1 13 8/1/2009 0 0 1 2010
1 14 9/1/2009 2500 2500 1 2010
1 15 10/1/2009 300 300 2 2010
1 16 11/1/2009 0 0 2 2010
1 17 12/1/2009 0 0 2 2010
1 18 1/1/2010 0 0 3 2010
2 1 8/1/2008 1200 1200 1 2009
2 2 9/1/2008 1500 1500 1 2009
2 3 10/1/2008 500 500 2 2009
2 4 11/1/2008 2100 2100 2 2009
2 5 12/1/2008 0 0 2 2009
2 6 1/1/2009 0 0 3 2009
2 7 2/1/2009 0 0 3 2009
2 8 3/1/2009 1600 1600 3 2009
2 9 4/1/2009 2000 2000 4 2009
2 10 5/1/2009 0 0 4 2009
2 11 6/1/2009 0 0 4 2009
2 12 7/1/2009 0 0 1 2010
2 13 8/1/2009 0 0 1 2010
2 14 9/1/2009 2500 2500 1 2010
2 15 10/1/2009 300 300 2 2010
2 16 11/1/2009 0 0 2 2010
2 17 12/1/2009 0 0 2 2010
2 18 1/1/2010 0 0 3 2010

The dates are headers to the columns, the id is already gotten, the 1
through 18 is just a count. The actual and the projected is where I
am having the problems. The first column is easy based on the code
you gave me, but I can't figure out how to make it move a column over
and print the next set of information. The quarter and fiscal years
aren't as important.

Thanks