why I am not to copy cells after blanks by using VBA code

[Learning1]

please see exmple below: when I ran my macro its only copy data up to Transection number and not all way to part number and I want all the columns in my break-up files.. I know I messing somewhere

and would appreciate the additional help. Thx a lot for your reply.

Set rng = cll.Resize(limit, cll.End(xlToRight).Column)


Set wrk = Application.Workbooks.Add
head.Copy wrk.Worksheets(1).Cells(1, 1)
rng.Copy wrk.Worksheets(1).Cells(2, 1)​

[TABLE="width: 982"]
<colgroup><col span="6"><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD]Change Type (Action)[/TD]
[TD]Adjustment Type[/TD]
[TD]Adjustment Code[/TD]
[TD]Transaction Type[/TD]
[TD]Transaction Usage Code[/TD]
[TD]Transaction Type Code[/TD]
[TD]Transaction Number[/TD]
[TD]Transaction Context[/TD]
[TD]Source Transaction Date[/TD]
[TD]Line ID[/TD]
[TD]Deal ID[/TD]
[TD]Product Family[/TD]
[TD]Part #[/TD]
[/TR]
[TR]
[TD]AL[/TD]
[TD]MANUAL[/TD]
[TD]RESTATE-SPVSS[/TD]
[TD]Manual[/TD]
[TD]R[/TD]
[TD]B[/TD]
[TD="align: right"]9999[/TD]
[TD][/TD]
[TD="align: right"]9/30/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CON-VSNT-TSS-SP-D[/TD]
[/TR]
[TR]
[TD]AL[/TD]
[TD]MANUAL[/TD]
[TD]RESTATE-SPVSS[/TD]
[TD]Manual[/TD]
[TD]R[/TD]
[TD]B[/TD]
[TD="align: right"]9999[/TD]
[TD][/TD]
[TD="align: right"]9/30/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CON-VSNT-TSS-SP-D[/TD]
[/TR]
</tbody>[/TABLE]

here is the code:

Sub doSplitRows()
Dim sht As Worksheet
Dim head As Range
Dim rng As Range
Dim limit As Integer
Dim cll As Range
Dim wrk As Workbook
Dim prefix As String
Dim i As Integer

Application.ScreenUpdating = False

'Workbook filename prefix
prefix = "test"

'Number of rows to create a new workbook
limit = 6000

Set sht = ActiveSheet
Set head = sht.Rows(1)
Set cll = sht.Cells(2, 1)
Do Until cll.Value = ""
i = i + 1
Set rng = cll.Resize(limit, cll.End(xlToRight).Column)


Set wrk = Application.Workbooks.Add
head.Copy wrk.Worksheets(1).Cells(1, 1)
rng.Copy wrk.Worksheets(1).Cells(2, 1)
wrk.SaveAs ThisWorkbook.Path & Application.PathSeparator & prefix & "_" & Format(i, "0000") 'Trying to make filename zero padded
wrk.Close
Set cll = cll.Offset(limit)
Loop

Application.ScreenUpdating = True

End Sub

 

[MARK858]

I don't really know why you need to use resize but anyway try...

Set rng = cll.Resize(limit, Cells(cll.Row, Columns.count).End(xlToLeft).Column)

 

[Learning1]

I don't really know why you need to use resize but anyway try...

Set rng = cll.Resize(limit, Cells(cll.Row, Columns.count).End(xlToLeft).Column)

Hi Mark, My friend thank you so much for your prompt response.. It worked like piece of cake now.. once again thank you and because of people like you we could learn a thing or two..