+ Reply to Thread
Results 1 to 7 of 7

Code not working!

Hybrid View

  1. #1
    Registered User
    Join Date
    05-23-2012
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    3

    Code not working!

    I created the following macro that will cut and paste info in every sheet of a workbook into the first one.
    Sub CutNPasteSheets()
    '
    ' This macro selects from worksheet 2 to the end-1 columns A to I and cut and paste the content
    ' to the first sheet of the workbook
    
        Dim I As Integer
        Dim mysheets As Integer
        
        mysheets = Sheets.Count
            
        
      For I = 2 To I = mysheets - 1
        
        Sheets(I).Select
        Range("A1:I1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Cut
        
        Sheets(1).Activate
        ActiveCell.Range("A1").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Range("A1").Select
           
        
        ActiveSheet.Paste
        Selection.End(xlDown).Select
        
      Next I
    
    End Sub
    I used Ctrl F8 to follow through to find out what it does nothing! and I still don't understand what I am doing wrong. It goes well until the loop start, then nothing happens.

    Please help
    Last edited by arlu1201; 05-23-2012 at 12:35 PM. Reason: Use code tags in future.

  2. #2
    Forum Contributor
    Join Date
    05-04-2012
    Location
    Stamford,Connecticut,USA
    MS-Off Ver
    Excel 2003
    Posts
    105

    Re: Can someone tell me why this is not working!

    Change this:
    For I = 2 To mysheets - 1

  3. #3
    Forum Contributor
    Join Date
    05-04-2012
    Location
    Stamford,Connecticut,USA
    MS-Off Ver
    Excel 2003
    Posts
    105

    Re: Can someone tell me why this is not working!

    This should be a bit more efficient:
    Sub CutNPasteSheets()
    '
    ' This macro selects from worksheet 2 to the end-1 columns A to I and cut and paste the content
    ' to the first sheet of the workbook
    
    Dim I As Integer
    Dim mysheets As Integer
    
    mysheets = Sheets.Count
    
    
    For I = 2 To mysheets - 1
    destrng = Sheets(1).Range("A65536").End(xlUp).Row + 1
    Sheets(I).Range(Range("A1:I1"), Range("A1:I1").End(xlDown)).Cut Destination:=Sheets(1).Range("A" & destrng)
    
    Next I
    
    End Sub

  4. #4
    Registered User
    Join Date
    05-23-2012
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Can someone tell me why this is not working!

    Thank you for the help. However, now I am getting this error Runtime error '1004' Application-defined or object-defined error. It appears when trying to execute "Sheets(I).Range(Range("A1:I1"), Range("A1:I1").End(xlDown)).Cut Destination:=Sheets(1).Range("A" & destrng)". In my original program it appears also when trying to execute 'Selection.End(xlDown).Select'

    Any clues?

  5. #5
    Forum Contributor
    Join Date
    05-04-2012
    Location
    Stamford,Connecticut,USA
    MS-Off Ver
    Excel 2003
    Posts
    105

    Re: Code not working!

    This usually happens when the last row is not properly defined. Try this:
    Sub CutNPasteSheets()
    '
    ' This macro selects from worksheet 2 to the end-1 columns A to I and cut and paste the content
    ' to the first sheet of the workbook
    
    Dim I As Integer
    Dim mysheets As Integer
    
    mysheets = Sheets.Count
    
    
    For I = 2 To mysheets - 1
    destrng = Sheets(1).Range("A65536").End(xlUp).Row + 1
    srcrng = Sheets(I).Range("A65536").End(xlUp).Row
    Sheets(I).Range("A1:I" & srcrng).Cut Destination:=Sheets(1).Range("A" & destrng)
    
    Next I
    
    End Sub

  6. #6
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Code not working!

    Alternate:
    Sub CutNPasteSheets()
    ' This macro selects from worksheet 2 to the end-1 columns A to I and cut and paste the content
    ' to the first sheet of the workbook
        
        Dim i As Integer
        
        For i = 2 To ActiveWorkbook.Sheets.Count - 1
            Intersect(Sheets(i).UsedRange, Sheets(i).Range("A:I")).Cut Sheets(1).Cells(Rows.Count, "A").End(xlUp).Offset(1)
        Next i
    
    End Sub
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  7. #7
    Registered User
    Join Date
    05-23-2012
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Code not working!

    Thank you very much for all your help. This time, it worked like a charm!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1