In the code below, I'm getting an error when I get to:

curWks.Cells(iRow, "A") = sh.Cells(1, colvalue).Value

---------------------------------------------------------------
Sub Data_Dump()

Dim curWks, ws As Worksheet
Dim FirstRow, Lastrow, Rowloop, colval As Integer

Application.ScreenUpdating = False
Set curWks = Worksheets("Data")
Set sh = Worksheets("All States")
curWks.Range("A2:D11376").Clear
iRow = 2
sh.Activate
For colval = 5 To 39
FirstRow = 2
Lastrow = 28
For Row_Loop = FirstRow To Lastrow
curWks.Cells(iRow, "A") = sh.Cells(1, colvalue).Value
curWks.Cells(iRow, "B") = sh.Cells(Row_Loop, 4).Value
curWks.Cells(iRow, "C") = sh.Cells(Row_Loop, colval).Value
iRow = iRow + 1
Next 'for
Next 'for
Application.ScreenUpdating = True

End Sub

Steve,

The problem is likely that you have a variable declared as "colval" but your
code is using "colvalue". You would not have encountered this problem --
the compiler would have caught it -- if you used "Option Explicit" at the
very top of the code module. See http://www.cpearson.com/excel/variables.htm
for information about declaring variables.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Steve" wrote in message
...
In the code below, I'm getting an error when I get to:

curWks.Cells(iRow, "A") = sh.Cells(1, colvalue).Value

---------------------------------------------------------------
Sub Data_Dump()

Dim curWks, ws As Worksheet
Dim FirstRow, Lastrow, Rowloop, colval As Integer

Application.ScreenUpdating = False
Set curWks = Worksheets("Data")
Set sh = Worksheets("All States")
curWks.Range("A2:D11376").Clear
iRow = 2
sh.Activate
For colval = 5 To 39
FirstRow = 2
Lastrow = 28
For Row_Loop = FirstRow To Lastrow
curWks.Cells(iRow, "A") = sh.Cells(1, colvalue).Value
curWks.Cells(iRow, "B") = sh.Cells(Row_Loop, 4).Value
curWks.Cells(iRow, "C") = sh.Cells(Row_Loop, colval).Value
iRow = iRow + 1
Next 'for
Next 'for
Application.ScreenUpdating = True

End Sub

typo .......

curWks.Cells(iRow, "A") = sh.Cells(1, colval).Value

"Steve" wrote:

In the code below, I'm getting an error when I get to:

curWks.Cells(iRow, "A") = sh.Cells(1, colvalue).Value

---------------------------------------------------------------
Sub Data_Dump()

Dim curWks, ws As Worksheet
Dim FirstRow, Lastrow, Rowloop, colval As Integer

Application.ScreenUpdating = False
Set curWks = Worksheets("Data")
Set sh = Worksheets("All States")
curWks.Range("A2:D11376").Clear
iRow = 2
sh.Activate
For colval = 5 To 39
FirstRow = 2
Lastrow = 28
For Row_Loop = FirstRow To Lastrow
curWks.Cells(iRow, "A") = sh.Cells(1, colvalue).Value
curWks.Cells(iRow, "B") = sh.Cells(Row_Loop, 4).Value
curWks.Cells(iRow, "C") = sh.Cells(Row_Loop, colval).Value
iRow = iRow + 1
Next 'for
Next 'for
Application.ScreenUpdating = True

End Sub