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

Learning1

New Member
Joined
Aug 13, 2018
Messages
7
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 TypeAdjustment CodeTransaction TypeTransaction Usage CodeTransaction Type CodeTransaction NumberTransaction ContextSource Transaction DateLine IDDeal IDProduct FamilyPart #
ALMANUALRESTATE-SPVSSManualRB99999/30/2018CON-VSNT-TSS-SP-D
ALMANUALRESTATE-SPVSSManualRB99999/30/2018CON-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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,705
Office Version
365, 2010
Platform
Windows, Mobile
I don't really know why you need to use resize but anyway try...
Code:
Set rng = cll.Resize(limit, Cells(cll.Row, Columns.count).End(xlToLeft).Column)
 
Last edited:

Learning1

New Member
Joined
Aug 13, 2018
Messages
7
I don't really know why you need to use resize but anyway try...
Code:
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..
 

Watch MrExcel Video

Forum statistics

Threads
1,099,916
Messages
5,471,487
Members
406,766
Latest member
Parasoner

This Week's Hot Topics

Top