Results 1 to 3 of 3

Thread: Missing data after copy rows data from different worksheets

  1. #1

    Missing data after copy rows data from different worksheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,
    I have attached my file for reference.
    Can anyone help me to check what is my mistakes?
    Thank you.

    Code:
    Sub CopySheets()
         
        Dim ws As Worksheet
        Dim lastrow As Long
        
        Application.ScreenUpdating = False
         
        For Each ws In Sheets(Array("LQ80", "LQ100", "LQ144A"))
        lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
            
            With ws
                .Range("A8:M" & CStr(lastrow)).Copy
                Worksheets("Final").Range("A" & CStr(lastrow)).PasteSpecial (xlPasteValues)
            End With
        
        Next ws
         
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
         
        Columns("K:K").ColumnWidth = 9
        Columns("L:L").ColumnWidth = 18
        Columns("M:M").ColumnWidth = 28
        
        Range("A1:J1").Select
        
    End Sub
    Attached Files Attached Files

  2. #2
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    Your code is copying a a range from each sheet, but lastrow is only determined on the ActiveSheet, so it misses data. Change ActiveSheet to ws.
    Hope that helps

    Roy

  3. #3
    Quote Originally Posted by royUK View Post
    Your code is copying a a range from each sheet, but lastrow is only determined on the ActiveSheet, so it misses data. Change ActiveSheet to ws.
    Hi royUK,

    I have amended my code as below but I got different result. Data is not missing but many blank rows are inserted in the ws.
    Click image for larger version. 

Name:	ouput_blank row.jpg 
Views:	6 
Size:	90.6 KB 
ID:	1290


    Code:
    Sub CopySheets()
        
        
        Dim ws As Worksheet
        Dim lastrow As Long
        Application.ScreenUpdating = False
        For Each ws In Sheets(Array("LQ80", "LQ100", "LQ144A"))
            lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
            With ws
                .Range("A8:M" & CStr(lastrow)).Copy
                Worksheets("Final").Range("A" & CStr(lastrow)).PasteSpecial (xlPasteValues)
            End With
        Next ws
         
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
         
        Columns("K:K").ColumnWidth = 9
        Columns("L:L").ColumnWidth = 18
        Columns("M:M").ColumnWidth = 28
        
        Range("A1:J1").Select
        
    End Sub

Posting Permissions

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