Help with Code possibly integer related

[stroffso]

Hi All,

I have the below piece of code which essentially takes data from one tab and makes it a bit more linear on another. So all the data (which is a timesheet) is on the timesheet tab. The code then changes it over to the history tab in a more linear form. Never had an issue with this until I went to add some columns which I had defined previously as “for k = 73 to 81” and made it to “for k = 73 to 101” which then did not work. However when I changed it to “for k = 73 to 90” it all worked fine and did everything as it should. When I went to “for k = 73 to 91” it did not work. Can anyone tell me what I am doing wrong here?

Sub Releasedarn()







Dim i As Integer 'count of rows in History Sheet



Dim j As Integer



Dim k As Integer ' count column



Dim l As Integer 'Sheet History Counter



Dim m As Integer



Dim x As String



Dim wb As Workbook











For k = 73 To 90



For j = 12 To 135



Set myRange = Sheets(“History”).Range("A:A")



i = Application.WorksheetFunction.CountA(myRange)



i = 1 + i



If (Sheets(“Timesheet”).Range("AN" & j).Value) = "Ready" Then



If (Sheets(“Timesheet”).Range(Chr(k) & j).Value) = "" Then







Else



'Copies all data to the history tab











Sheets("History").Range("A" & i).Value = Sheets(“Timesheet”).Range("C2").Value



Sheets(“History”).Range("B" & i).Value = Sheets(“Timesheet”).Range("F3").Value



Sheets(“History”).Range("C" & i).Value = Sheets(“Timesheet”).Range("F2").Value



Sheets(“History”).Range("D" & i).Value = "=Text(C" & i & ",""mmm"")"



Sheets(“History”).Range("E" & i).Value = Sheets(“Timesheet”).Range("C" & j).Value



Sheets(“History”).Range("F" & i).Value = Sheets(“Timesheet”).Range("B" & j).Value



Sheets(“History”).Range("G" & i).Value = Sheets(“Timesheet”).Range(Chr(k) & "4").Value



Sheets(“History”).Range("H" & i).Value = Sheets(“Timesheet”).Range(Chr(k) & "5").Value







Sheets(“History”).Range("I" & i).Value = Sheets(“Timesheet”).Range(Chr(k) & "6").Value



Sheets(“History”).Range("J" & i).Value = Sheets(“Timesheet”).Range(Chr(k) & "7").Value







Sheets(“History”).Range("K" & i).Value = Sheets(“Timesheet”).Range("A" & j).Value



Sheets(“History”).Range("L" & i).Value = Sheets(“Timesheet”).Range(Chr(k) & "8").Value



Sheets(“History”).Range("M" & i).Value = "\" & Sheets(“Timesheet”).Range(Chr(k) & "7").Value & "." & Sheets(“Timesheet”).Range("A" & j).Value & Sheets(“Timesheet”).Range(Chr(k) & "8").Value



Sheets(“History”).Range("N" & i).Value = Sheets(“Timesheet”).Range(Chr(k) & j).Value



Sheets(“History”).Range("O" & i).Value = Sheets(“Timesheet”).Range("H" & j).Value







If j > 74 Then



Sheets(“History”).Range("P" & i).Value = Sheets(“Timesheet”).Range("F" & j).Value



Sheets(“History”).Range("Q" & i).Value = Sheets(“Timesheet”).Range("e" & j).Value



End If







Sheets(“History”).Range("R" & i).Formula = "=N" & i & "*O" & i



Sheets(“History”).Range("S" & i).Value = Sheets(“Timesheet”).Range("AM" & j).Value



Sheets(“History”).Range("T" & i).Value = "No" 'comment for reciepting







End If



End If







Next j



Next k

End Sub

 

[My Aswer Is This]

Would you please tell me in words what your wanting this script to do.
All you said was:

I have the below piece of code which essentially takes data from one tab and makes it a bit more linear on another.​

What does this mean:

bit more linear​

 

[stroffso]

So the data on the timesheet tab goes across the page with a work order for each column. One employee per row. The code is to transfer this data line by line so each work order for each person has a row to itself. I had 10 columns initially for work orders and the code worked fine but then tried a lot more and it didn’t work, when I reduced slightly it did.

so let’s say one row has an employee with 4 work order columns populated on the timesheet tab, it will then transfer to be 4 rows on the history tab.

 

[My Aswer Is This]

Try this:

I had to change the sheet name.
My Excel says I cannot use a sheet named "History"
So you will see I used the name Data
Modify Data to what you want.

Sub Copy_Rows()
''Modified  11/29/2019  3:38:51 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim LastColumn As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim ans As String
ans = "Data"
For i = 1 To Lastrow
    LastColumn = Cells(i, Columns.Count).End(xlToLeft).Column
    Lastrowa = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
    Rows(i).Copy Sheets(ans).Cells(Lastrowa, 1).Resize(LastColumn)
Next
Application.ScreenUpdating = True
End Sub

 

[stroffso]

Sorry but where in my code would I put what you have written above as I presume I would need to keep the top part of what I had already?

 

[stroffso]

Thanks for that macro but it doesnt seem to do what is needed its more copying and pasting everything in a load of times but not in the way that is needed, doesnt help that im probably not explaining it very well, really apprecaite your help. its just that one piece of my initial code that is the issue here "

For k = 73 To 90
"

If someone can explain why i cant go over 90 with this that would be great

 

[RoryA]

What exactly does "doesn't work" mean? What happens?

 

[My Aswer Is This]

I thought you said if row 1 had 5 columns of data then you wanted this row pasted into sheet History 5 times
And if row(2) had 3 columns of data you wanted this row pasted into 3 rows on sheet named History

I'm not interested in trying to modify your code which does not work.

 

[Fluff]

You problem is that you are converting the value of k into a character, that is then being used as the column letter for a range

Range(Chr(k) & j).

Character 90 is Z, but character 91 is [ which is not a valid column.
Change this For k = 73 To 90 to For k= 9 to 37 and then wherever you have Range(Chr(k) & j) change it to Cells(j,k)

 

[stroffso]

I thought you said if row 1 had 5 columns of data then you wanted this row pasted into sheet History 5 times
And if row(2) had 3 columns of data you wanted this row pasted into 3 rows on sheet named History

I'm not interested in trying to modify your code which does not work.

thank you, I didn’t actually ask you to modify the code I was just asking a reason why it didn’t work but thanks for taking the time anyhow.