CODEWITHSUNDEEP
excelvba
mohneesh malik
mohneesh malik

Reputation: 1

Round up to next whole number

I have data in sheet1 (master sheet) to be divided by 300 and then pasted in every sheet.

These sheets are created by a VBA code for loop.

If my lastrow gives a decimal value I found problems.

e.g. I calculate the Last Row of MYdata and divide it by 300 as I need data in every sheet max 300. If my lastrow data is 1342, dividing by 300 I get 4.473333333. This creates only 4 sheets. I require 5 sheets of data.

Sub test() 

Dim lrow As Long, lrow1 As Double 
lrow = Sheet3.Range("a65000").End(xlUp).Row 
lrow1 = Sheet3.Range("a65000").End(xlUp).Row 
num = lrow / 300 

For x = 1 To num 
    Sheets.Add 
    ActiveSheet.Name = x 
    Sheet3.Activate 
    Sheet3.Range("a2:a301").Select 
    Sheet3.Range("a2:a301").Cut 
    Sheets(x).Select Range("a1").Select 
    ActiveSheet.Paste 
    Sheet3.Select 
    Selection.Delete Shift:=xlUp 
Next x 

End Sub

Upvotes: 0

Views: 50

Answers (1)

urdearboy
urdearboy

Reputation: 14580

This will always round you to the next whole number

num = Round((lrow / 300) + 0.5, 1)

Some suggested updates

Option Explicit

Sub Parse ()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet3")
Dim ns As Worksheet 'New Sheet
Dim lrow As Long, x

lrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

For x = 1 To Round((lrow / 300) + 0.5, 1)
    Set ns = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ns.Name = x
        ws.Range("A2:A301").Copy ns.Range("A1")
        ws.Range("A2:A301").Delete Shift:=xlUp
Next x

End Sub

Upvotes: 1

Related Questions