+ Reply to Thread
Results 1 to 8 of 8

Need cut and paste data from multiple sheets to one master sheet

Hybrid View

  1. #1
    Registered User
    Join Date
    01-04-2013
    Location
    dayton ohio
    MS-Off Ver
    Excel 2010
    Posts
    34

    Question Need cut and paste data from multiple sheets to one master sheet

    Data on sheet 1, sheet 2, sheet 3 are in the same format and layout. Need to copy data from these three sheets to one master sheet(sheet 4). How am I supposed to find last row of the master sheet and paste data to last row+1? Also after this first step is finished, delete all data on sheet 1 to sheet 3. Thanks.

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Need cut and paste data from multiple sheets to one master sheet

    There are many unknowns on your request; it would be easier if you could attach your sample workbook. Please go to advance-half way the page, you see an attachment icon, or button.

  3. #3
    Registered User
    Join Date
    01-04-2013
    Location
    dayton ohio
    MS-Off Ver
    Excel 2010
    Posts
    34

    Question Re: Need cut and paste data from multiple sheets to one master sheet

    I need to cut and paste from row 2 to last row that has data in each worksheet (sheet 1 to sheet 3) to the master worksheet.
    Thanks.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Need cut and paste data from multiple sheets to one master sheet

    Sub CombineAllSheets()
     Dim ms As Worksheet, LRms As Long, ws As Worksheet, LR As Long, rng As Long, i As Long
     
        Application.ScreenUpdating = 0
        Application.DisplayAlerts = 0
        
        On Error Resume Next
        
        If Not Evaluate("ISREF(Master!A1)") Then
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Master"
        Else
            Set ms = Sheets("Master")
            Sheets("Master").Range("A2:D" & Rows.Count).ClearContents
        End If
         
         Worksheets(1).Rows(1).Copy ms.Range("A1")
         
     For Each ws In Worksheets
        With ws
            If .Name <> "Master" Then
                LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
                .Range("A2:D" & LR).Cut ms.Range("A" & ms.Range("A:P").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1).Resize(, 16)
                 
            End If
        End With
     Next ws
         
         Application.CutCopyMode = 0
         
         Application.ScreenUpdating = 1
        
         Application.DisplayAlerts = 1
    End Sub
    Last edited by AB33; 01-06-2013 at 06:45 PM.

  5. #5
    Registered User
    Join Date
    01-04-2013
    Location
    dayton ohio
    MS-Off Ver
    Excel 2010
    Posts
    34

    Re: Need cut and paste data from multiple sheets to one master sheet

    Thank you. Here is another scenario. I want to cut and paste data on the first three tabs only to the master worksheet. But I only want to copy the rows that have a value in column A. The next procedure is after the data are pasted, this time delete the value from range E2:H5 only. Again, thanks for your help.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Need cut and paste data from multiple sheets to one master sheet

    "But I only want to copy the rows that have a value in column A". Is it column A values ONLY you wish to copy, not columns B, C as you have header for these columns in master?

  7. #7
    Registered User
    Join Date
    01-04-2013
    Location
    dayton ohio
    MS-Off Ver
    Excel 2010
    Posts
    34

    Re: Need cut and paste data from multiple sheets to one master sheet

    Sorry about the confusion. Copy these rows that have a value in column A,do not copy rows that are either blank or "-" in column A.

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Need cut and paste data from multiple sheets to one master sheet

    It is not clear to me what your requirements are, but try this one and can be improved

    Sub CombineAllSheets1()
     Dim ms As Worksheet, ws As Worksheet, LR As Long, i As Long,LRms As Long
     
        Application.ScreenUpdating = 0
        Application.DisplayAlerts = 0
        
        On Error Resume Next
        
        If Not Evaluate("ISREF(Master!A1)") Then
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Master"
        Else
            Set ms = Sheets("Master")
            Sheets("Master").Range("A2:C" & Rows.Count).ClearContents
        End If
         
         Worksheets(1).Range("A1").Resize(, 3).Copy ms.Range("A1").Resize(, 3)
         
     For Each ws In Worksheets
        With ws
            If .Name <> "Master" And .Name <> "Other Sheets" Then
                LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
                 .Range("A2:C" & LR).Copy
                 ms.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    
            End If
            
        End With
        
     Next ws
     
       For Each ws In Worksheets
      
            With ws
      
                If .Name <> "Master" And .Name <> "Other Sheets" Then
                    LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
      
                    .Range("E2:H" & LR).EntireRow.Delete
                End If
                
            End With
      
       Next ws
      
      
            With ms
              LRms = ms.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            
                For i = LRms To 2 Step -1
                    If .Cells(i, "A") = "" Or .Cells(i, "A") = "-" Then
                    Rows(i).EntireRow.Delete
                    End If
                    .Columns.AutoFit
                Next i
            End With
         
         Application.CutCopyMode = 0
         
         Application.ScreenUpdating = 1
        
         Application.DisplayAlerts = 1
         
    End Sub
    Last edited by AB33; 01-07-2013 at 06:07 AM.

+ 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