I have a 28 column input spreadsheet which I need to convert to 6 columns for
Database input. I have to sheets in the workbook, "Input" and "Output" Please
help with the VBA below, I am asked to debug from Inpt.Range

Sub rearrange_data()
Application.ScreenUpdating = False
For i = 2 To InputBox("How many lines?")
For c = 1 To 24
Inpt.Range(Cells(i, 1), Cells(i, 3)).Copy
Output.Cells(((i - 1) * 24) - 24 + c, 1).PasteSpecial xlValues
Inpt.Cells(i, c + 4).Copy
Output.Cells(((i - 1) * 24) - 24 + c, 6).PasteSpecial xlValues
If c <= 12 Then
Output.Cells(((i - 1) * 24) - 24 + c, 5) = "2009"
Else
Output.Cells(((i - 1) * 24) - 24 + c, 5) = "2010"
End If

Select Case c
Case 1
Output.Cells(((i - 1) * 24) - 24 + c, 4) = "01"
Case 2
Output.Cells(((i - 1) * 24) - 24 + c, 4) = "02"
Case 3
Case 24
Output.Cells(((i - 1) * 24) - 24 + c, 4) = "12"
End Select
Next c

Next i
Output.Activate

Application.CutCopyMode = False
Range("A1").Select
Selection.EntireRow.Insert
Range("A1").Select
ActiveCell.FormulaR1C1 = "Username"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Project"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Proj name"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Month"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Year"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Hours"
Range("A1").Select
Selection.AutoFilter
Range("F1").Select
Selection.AutoFilter Field:=6, Criteria1:="="
Range("F2:F65000").Select
Selection.EntireRow.Delete
Selection.AutoFilter Field:=6

Application.ScreenUpdating = True
End Sub

Hi

Is Inpt and Output supposed to be variables refering to the sheets or ?

If the first is the case, I think you need to declare the variables in the
macro.

Try this before the loop :

Dim Inpt as Worksheet
Dim Output as Worksheet
Set Inpt=Worksheets("Input")
Set Output=Worksheets("Output")

Hopes this helps.

---
Per

"Chase" skrev i meddelelsen
...
I have a 28 column input spreadsheet which I need to convert to 6 columns
for
Database input. I have to sheets in the workbook, "Input" and "Output"
Please
help with the VBA below, I am asked to debug from Inpt.Range

Sub rearrange_data()
Application.ScreenUpdating = False
For i = 2 To InputBox("How many lines?")
For c = 1 To 24
Inpt.Range(Cells(i, 1), Cells(i, 3)).Copy
Output.Cells(((i - 1) * 24) - 24 + c, 1).PasteSpecial xlValues
Inpt.Cells(i, c + 4).Copy
Output.Cells(((i - 1) * 24) - 24 + c, 6).PasteSpecial xlValues
If c <= 12 Then
Output.Cells(((i - 1) * 24) - 24 + c, 5) = "2009"
Else
Output.Cells(((i - 1) * 24) - 24 + c, 5) = "2010"
End If

Select Case c
Case 1
Output.Cells(((i - 1) * 24) - 24 + c, 4) = "01"
Case 2
Output.Cells(((i - 1) * 24) - 24 + c, 4) = "02"
Case 3
Case 24
Output.Cells(((i - 1) * 24) - 24 + c, 4) = "12"
End Select
Next c

Next i
Output.Activate

Application.CutCopyMode = False
Range("A1").Select
Selection.EntireRow.Insert
Range("A1").Select
ActiveCell.FormulaR1C1 = "Username"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Project"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Proj name"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Month"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Year"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Hours"
Range("A1").Select
Selection.AutoFilter
Range("F1").Select
Selection.AutoFilter Field:=6, Criteria1:="="
Range("F2:F65000").Select
Selection.EntireRow.Delete
Selection.AutoFilter Field:=6

Application.ScreenUpdating = True
End Sub

Hi Per

I did as you suggested but the debug is still going to the row strating
Inpt.Range


"Per Jessen" wrote:

Hi

Is Inpt and Output supposed to be variables refering to the sheets or ?

If the first is the case, I think you need to declare the variables in the
macro.

Try this before the loop :

Dim Inpt as Worksheet
Dim Output as Worksheet
Set Inpt=Worksheets("Input")
Set Output=Worksheets("Output")

Hopes this helps.

---
Per

"Chase" skrev i meddelelsen
...
I have a 28 column input spreadsheet which I need to convert to 6 columns
for
Database input. I have to sheets in the workbook, "Input" and "Output"
Please
help with the VBA below, I am asked to debug from Inpt.Range

Sub rearrange_data()
Application.ScreenUpdating = False
For i = 2 To InputBox("How many lines?")
For c = 1 To 24
Inpt.Range(Cells(i, 1), Cells(i, 3)).Copy
Output.Cells(((i - 1) * 24) - 24 + c, 1).PasteSpecial xlValues
Inpt.Cells(i, c + 4).Copy
Output.Cells(((i - 1) * 24) - 24 + c, 6).PasteSpecial xlValues
If c <= 12 Then
Output.Cells(((i - 1) * 24) - 24 + c, 5) = "2009"
Else
Output.Cells(((i - 1) * 24) - 24 + c, 5) = "2010"
End If

Select Case c
Case 1
Output.Cells(((i - 1) * 24) - 24 + c, 4) = "01"
Case 2
Output.Cells(((i - 1) * 24) - 24 + c, 4) = "02"
Case 3
Case 24
Output.Cells(((i - 1) * 24) - 24 + c, 4) = "12"
End Select
Next c

Next i
Output.Activate

Application.CutCopyMode = False
Range("A1").Select
Selection.EntireRow.Insert
Range("A1").Select
ActiveCell.FormulaR1C1 = "Username"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Project"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Proj name"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Month"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Year"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Hours"
Range("A1").Select
Selection.AutoFilter
Range("F1").Select
Selection.AutoFilter Field:=6, Criteria1:="="
Range("F2:F65000").Select
Selection.EntireRow.Delete
Selection.AutoFilter Field:=6

Application.ScreenUpdating = True
End Sub

Hi Per
Thanks for your help. I re-wrote the whole script in a new file and got the
process to work.
Regards
Chase

"Per Jessen" wrote:

Hi

You need to set the sheet reference for the cells too:

Inpt.Range(Inpt.Cells(i, 1), Inpt.Cells(i, 3)).Copy

Regards,
Per

"Chase" skrev i meddelelsen
...
Hi Per

I did as you suggested but the debug is still going to the row strating
Inpt.Range


"Per Jessen" wrote:

Hi

Is Inpt and Output supposed to be variables refering to the sheets or ?

If the first is the case, I think you need to declare the variables in
the
macro.

Try this before the loop :

Dim Inpt as Worksheet
Dim Output as Worksheet
Set Inpt=Worksheets("Input")
Set Output=Worksheets("Output")

Hopes this helps.

---
Per

"Chase" skrev i meddelelsen
...
I have a 28 column input spreadsheet which I need to convert to 6
columns
for
Database input. I have to sheets in the workbook, "Input" and "Output"
Please
help with the VBA below, I am asked to debug from Inpt.Range

Sub rearrange_data()
Application.ScreenUpdating = False
For i = 2 To InputBox("How many lines?")
For c = 1 To 24
Inpt.Range(Cells(i, 1), Cells(i, 3)).Copy
Output.Cells(((i - 1) * 24) - 24 + c, 1).PasteSpecial xlValues
Inpt.Cells(i, c + 4).Copy
Output.Cells(((i - 1) * 24) - 24 + c, 6).PasteSpecial xlValues
If c <= 12 Then
Output.Cells(((i - 1) * 24) - 24 + c, 5) = "2009"
Else
Output.Cells(((i - 1) * 24) - 24 + c, 5) = "2010"
End If

Select Case c
Case 1
Output.Cells(((i - 1) * 24) - 24 + c, 4) = "01"
Case 2
Output.Cells(((i - 1) * 24) - 24 + c, 4) = "02"
Case 3
Case 24
Output.Cells(((i - 1) * 24) - 24 + c, 4) = "12"
End Select
Next c

Next i
Output.Activate

Application.CutCopyMode = False
Range("A1").Select
Selection.EntireRow.Insert
Range("A1").Select
ActiveCell.FormulaR1C1 = "Username"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Project"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Proj name"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Month"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Year"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Hours"
Range("A1").Select
Selection.AutoFilter
Range("F1").Select
Selection.AutoFilter Field:=6, Criteria1:="="
Range("F2:F65000").Select
Selection.EntireRow.Delete
Selection.AutoFilter Field:=6

Application.ScreenUpdating = True
End Sub