copy to vba

[billandrew]

good evening

trying to copy all values (numbers 1 through 4) in column e to a new appropriate worksheet. For some reason not all data is copied over.

There are many spaces between rows, do not believe this is the reason? Used this code before, no issue in past.

Any help would be appreciated


Code using.

Dim i As Long
Dim lastrow As Long
Dim lastrow2 As Long
Dim sh As string
Sheets("Sheet2A").Activate



Application.ScreenUpdating = False


lastrow = Sheets("Sheet2A").Range("E" & Rows.Count).End(xlUp).Row

For i = 2 To lastrow
If Cells(i, "E").Value <> "" Then
result = Cells(i, "E").Value
Sheets("Sheet2A").Rows(1).Copy Destination:=Sheets(sh).Rows(1)


lastrow2 = Sheets(sh).Cells(Rows.Count, "A").End(xlUp).Row + 1

Rows(i).Copy Destination:=Sheets(sh).Rows(lastrow2)



End If

Next i


Application.ScreenUpdating = True

Exit Sub




End Sub

 

[billandrew]

There are no extra spaces in the sheet name or in cell E11

 

[billandrew]

I am adding the following (sheets) at the start of the supplied code

With ThisWorkbook

.Sheets.Add(after:=.Sheets(.Sheets.Count)).Name = "1"
.Sheets.Add(after:=.Sheets(.Sheets.Count)).Name = "2"
.Sheets.Add(after:=.Sheets(.Sheets.Count)).Name = "3"
.Sheets.Add(after:=.Sheets(.Sheets.Count)).Name = "4"

End With

 

[Joe4]

But you said:

result = 11

So it is looking for sheet Name "11", not "1", "2", "3", or "4"...

 

[billandrew]

As a check I wrote the following to see if all the values in column E "2" would be copied over. This code worked. However I would need to replicate this 3 more times. Not sure what I an doing wrong here...

Sub copyValue_2()

Dim i As Long
Dim lastrow As Long
Dim lastrow2 As Long



lr = Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Row

Sheets("Sheet2").Activate

For i = 2 To lr

If Cells(i, "E").Value = "2" Then
lastrow2 = Sheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row
Rows(i).Copy Destination:=Sheets("Sheet4").Range("A" & lastrow2 + 1)


End If

Next i



End Sub

 

[billandrew]

Not sure why it would look for sheet name 11.

 

[Joe4]

Not sure why it would look for sheet name 11.

That is what you said back in post 9:

I did not place the break
error is Run Time error 9
result = 11
Yes there is a sheet by this name in the workbook

The code does run after I debug, however not all the data is copied over

That would seem to suggest that you have an 11 somewhere in column E.

 

[billandrew]

yes there is

 

[billandrew]

Yes there is one cell with an 11

 

[Joe4]

Yes there is one cell with an 11

You need to come up with a plan for how to account for values in column E for which you have no sheets with that name.

 

[billandrew]

Thank You very much for the time and effort. Would not have figured it out myself