+ Reply to Thread
Results 1 to 7 of 7

Code not working!

Hybrid View

  1. 05-23-2012, 10:30 AM #1
    suivanova
    suivanova is offline
    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.
    Reply With QuoteRegister To Reply
  2. 05-23-2012, 10:34 AM #2
    RaulRodriguez
    RaulRodriguez is offline
    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
    Reply With QuoteRegister To Reply
  3. 05-23-2012, 10:42 AM #3
    RaulRodriguez
    RaulRodriguez is offline
    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
    Reply With QuoteRegister To Reply
  4. 05-23-2012, 12:13 PM #4
    suivanova
    suivanova is offline
    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?
    Reply With QuoteRegister To Reply
  5. 05-23-2012, 01:02 PM #5
    RaulRodriguez
    RaulRodriguez is offline
    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
    Reply With QuoteRegister To Reply
  6. 05-23-2012, 01:11 PM #6
    tigeravatar
    tigeravatar is offline
    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
    Reply With QuoteRegister To Reply
  7. 05-23-2012, 01:46 PM #7
    suivanova
    suivanova is offline
    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1