CODEWITHSUNDEEP
excelvba
C Jones
C Jones

Reputation: 135

Excel macro Do While loop not compiling

Its a simple code to go to a sheet pull a row, go back to the first one sheet and paste it, then repeat until the value in column A of the inventory changes (New employee) at which point it needs to make a new worksheet to start storing the new data. And repeat until its done. 

Dim i As Integer
Dim j As Integer

Set i = 2
Set j = 1
Do While i < 6
    Sheets("Inventory").Select
    If Cells("i,1").Value = Cells("i-1,1").Value Then
        Cells("i:i").Select
        Selection.Copy
        Sheets("Sheetj").Select
        Cells("i,1").Select
        Selection.Paste
        i = i + 1
    Else
        Sheets.Add After:=Sheets(Sheets.Count)
        j = j + 1
        Sheets("Inventory").Select
        Cells("i:i").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheetj").Select
        Range("A3").Select
        ActiveSheet.Paste
        i = i + 1
    End If

End Sub

Upvotes: 0

Views: 712

Answers (4)

brettdj
brettdj

Reputation: 55682

I would do something like this to add create new sheets for each data group.

Updated: reduced my code now your "sheetj" part is clear

code

Sub Other()   
Dim rng1 As Range
Dim rng2 As Range
Dim ws As Worksheet
Set rng1 = Sheets("Inventory").Range("I2:i6")
Set ws = Sheets.Add
For Each rng2 In rng1
    If rng2 <> rng2.Offset(-1, 0) Then Set ws = Sheets.Add
    rng2.EntireRow.Copy ws.Rows(rng2.Row)
Next
End Sub

Upvotes: 1

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1654

Untested, but i think you use too many selects (tried with .activate ?)

Dim i As long 'long is faster for loops
Dim j As long

i = 2 'dont need set 
j = 1
Do While i < 6
    with Sheets("Inventory")
       If .Cells(i,1).Value = .Cells(i-1,1).Value Then  'i removed the quotes in cells
           .range("i:i").Copy Sheets("Sheetj").Cells(i,1)
           i = i + 1
       Else
           Sheets.Add After:=Sheets(Sheets.Count)
           j = j + 1
           .Cells("i:i").copy Sheets("Sheetj").Range("A3")
           i = i + 1
       End If
    end with
    Application.CutCopyMode = False
loop  'you forgot a ending loop

Upvotes: 0

SeekingAlpha
SeekingAlpha

Reputation: 7817

Sub test()

Dim i As Integer
Dim j As Integer

i = 2 ' got rid of set
j = 1 ' got rid of set
Do While i < 6
    Sheets("Inventory").Select
    If Cells("i,1").Value = Cells("i-1,1").Value Then
        Cells("i:i").Select
        Selection.Copy
        Sheets("Sheetj").Select
        Cells("i,1").Select
        Selection.Paste
        i = i + 1
    Else
        Sheets.Add After:=Sheets(Sheets.Count)
        j = j + 1
        Sheets("Inventory").Select
        Cells("i:i").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheets" & j).Select ' for completeness...
        Range("A3").Select
        ActiveSheet.Paste
        i = i + 1
    End If
    Loop ' missing this


End Sub

Upvotes: 0

Alex D
Alex D

Reputation: 696

Add this:

Loop

Before you end the sub. The i's also shouldn't have double quote if you're referencing what the number I should be. Should be like Cells(i , 1), or Cells(i , i), I'll leave you up to fix the rest.

Sorry, misread your original post. fixed.

Upvotes: 1

Related Questions