Click here to Skip to main content
13,798,497 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
I am merging some spreadsheets into one summary sheet, but I do not want the data copied (from the multiple sheets) to start until row/column D9 and I do not want it to start the paste on the summary sheet until D9. Here is my current code... How do I add the other directives?

Sub CopyDataWithoutHeaders()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim shLast As Long
    Dim CopyRng As Range
    Dim StartRow As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    ' Delete the summary sheet if it exists.
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("Comprehensive").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    ' Add a new summary worksheet.
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "Comprehensive"

    ' Fill in the start row.
    StartRow = 2

    ' Loop through all worksheets and copy the data to the
    ' summary worksheet.
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> DestSh.Name Then

            ' Find the last row with data on the summary
            ' and source worksheets.
            Last = LastRow(DestSh)
            shLast = LastRow(sh)

            ' If source worksheet is not empty and if the last
            ' row >= StartRow, copy the range.
            If shLast > 0 And shLast >= StartRow Then
                'Set the range that you want to copy
                Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

               ' Test to see whether there are enough rows in the summary
               ' worksheet to copy all the data.
                If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                   MsgBox "There are not enough rows in the " & _
                   "summary worksheet to place the data."
                   GoTo ExitTheSub
                End If

                ' This statement copies values and formats.
                CopyRng.Copy
                With DestSh.Cells(Last + 1, "A")
                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteFormats
                    Application.CutCopyMode = False
                End With

            End If

        End If
    Next

ExitTheSub:

    Application.Goto DestSh.Cells(1)

    ' AutoFit the column width in the summary sheet.
    DestSh.Columns.AutoFit

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub


What I have tried:

nothing yet. just using the VB code already described...
Posted 11-Jul-17 7:49am
Updated 12-Jul-17 3:27am
v2
Comments
Richard MacCutchan 11-Jul-17 15:10pm
   
There is an easy way to resolve questions like this. Use the macro recording feature of Excel. Start the recording, perform the steps you need, stop the recording and examine the generated code.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Here is a version of your code that copies data from D9 to the end of the data used and pastes the results into the [Comprehensive] sheet starting at cell D9 instead of A2. It copies the data present from D9 to the last column rather than attempting to copy all of the columns for the rows used otherwise there would be not enough columns for the copy to take place.
I'll leave it to you to spot the differences
Option Explicit

Sub CopyDataWithoutHeaders()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim shLast As Long
    Dim CopyRng As Range
    Dim StartRow As Long
 
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
 
    ' Delete the summary sheet if it exists.
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("Comprehensive").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
 
    ' Add a new summary worksheet.
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "Comprehensive"
 
    ' Fill in the start row.
    StartRow = 9
 
    ' Loop through all worksheets and copy the data to the
    ' summary worksheet.
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> DestSh.Name Then
 
            ' Find the last row with data on the summary
            ' and source worksheets.
            Last = LastRow(DestSh)
            shLast = LastRow(sh)
            
            If Last < 9 Then Last = 8   'As we add +1 later
 
            'Find the last cell used on the source worksheet courtesy of Andy Pope, OzMVP
            Dim lastCell As String
            lastCell = Replace(Cells(1, sh.UsedRange.Columns.Count).Address(False, False), "1", CStr(shLast))
            
 
            ' If source worksheet is not empty and if the last
            ' row >= StartRow, copy the range.
            If shLast > 0 And shLast >= StartRow Then
                'Set the range that you want to copy
                Set CopyRng = sh.Range("D" & CStr(StartRow) & ":" & lastCell)
 
               ' Test to see whether there are enough rows in the summary
               ' worksheet to copy all the data.
                If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                   MsgBox "There are not enough rows in the " & _
                   "summary worksheet to place the data."
                   GoTo ExitTheSub
                End If
 
                ' This statement copies values and formats.
                CopyRng.Copy
                With DestSh.Cells(Last + 1, "D")
                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteFormats
                    Application.CutCopyMode = False
                End With
 
            End If
 
        End If
    Next
 
ExitTheSub:
 
    Application.Goto DestSh.Cells(1)
 
    ' AutoFit the column width in the summary sheet.
    DestSh.Columns.AutoFit
 
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub
Private Function LastRow(ws As Worksheet) As Long

    LastRow = ws.UsedRange.Rows.Count

End Function
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web05 | 2.8.181207.3 | Last Updated 12 Jul 2017
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100