+ Reply to Thread
Results 1 to 3 of 3

Copying and Pasting Rows to a different worksheet

Hybrid View

  1. #1
    Registered User
    Join Date
    06-21-2010
    Location
    Texas, San Antonio
    MS-Off Ver
    Excel 2007
    Posts
    57

    Copying and Pasting Rows to a different worksheet

    Hello,
    I have a excel sheet called Sheet1 which has multiple rows of data. What I want to do is have the rows with data in them to be copied and pasted to another sheet called Data. I wrote some VBA to help me with this, but I think my code is too long and I was wandering if there was a way to condense it. Also is there a way to determine if no data is a row to not copy it over to the Sheet called Data.
    Here is the code:

    Option Explicit
    Sub DataEnter_Click()
    Dim nextrow As Integer
     nextrow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row + 1
     If Sheets("Sheet1").Range("A7").Value > 0 Then
        Sheets("Sheet1").Range("A7:F7").Copy
            If Sheets("Sheet1").Range("A7").Value And Sheets("Sheet1").Range("A8").Value > 0 Then
                Sheets("Sheet1").Range("A7:I8").Copy
                    If Sheets("Sheet1").Range("A7").Value And Sheets("Sheet1").Range("A8").Value And Sheets("Sheet1").Range("A9").Value > 0 Then
                        Sheets("Sheet1").Range("A7:I9").Copy
     Sheets("Data").Range("A" & nextrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
     
     End If
        End If
            End If
     End Sub

    Thanks
    Last edited by Coldsteel; 09-13-2010 at 10:35 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Copying and Pasting Rows to a different worksheet

    Maybe:
    Sub DataEnter_Click()
    Dim NR As Long
    
    NR = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row + 1
     
    With Sheets("Sheet1")
        If .Range("A7").Value > 0 Then
            .Range("A7:F7").Resize(Application.WorksheetFunction.CountIf(Range("A7:A9"), ">0")).Copy
            Sheets("Data").Range("A" & NR).PasteSpecial xlPasteValues
        End If
    End With
    
    End Sub
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    06-21-2010
    Location
    Texas, San Antonio
    MS-Off Ver
    Excel 2007
    Posts
    57

    Re: Copying and Pasting Rows to a different worksheet

    Thanks, the code works great

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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