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)​

Change Type (Action)

Adjustment Type

Adjustment Code

Transaction Type

Transaction Usage Code

Transaction Type Code

Transaction Number

Transaction Context

Source Transaction Date

Line ID

Deal ID

Product Family

Part #

AL

MANUAL

RESTATE-SPVSS

Manual

R

B

9999

9/30/2018

CON-VSNT-TSS-SP-D

AL

MANUAL

RESTATE-SPVSS

Manual

R

B

9999

9/30/2018

CON-VSNT-TSS-SP-D

<colgroup><col span="6"><col><col><col><col span="4"></colgroup><tbody>
</tbody>

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..