+ Reply to Thread
Results 1 to 15 of 15

append copied data to the last used row

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Hyderbad
    MS-Off Ver
    Excel 2003
    Posts
    123

    append copied data to the last used row

    Sub ConsolidateCommSheetFromAllWbk()
        Dim folpath As String
            With Application.FileDialog(msoFileDialogFolderPicker)
            .Show
            folpath = .SelectedItems(1)
            End With
        Dim fs, fol As Object, f
        Dim wbk As Workbook
            Set fs = CreateObject("scripting.filesystemobject")
            Set fol = fs.getfolder(folpath)
            Sheets(1).Cells.Clear
            'Sheets(1).Cells(1, 1) = "Heading-1"
        Dim shtname As String
            shtname = InputBox("Enter SheetName")
            For Each f In fol.Files
            If UCase(fs.getextensionname(f.Name)) = "XLSX" Or _
            UCase(fs.getextensionname(f.Name)) = "XLS" Then
            Set wbk = Workbooks.Open(f.Path)
            wbk.Sheets(shtname).Rows("1:" & Sheets(shtname).UsedRange.Rows.Count).Copy _
            ThisWorkbook.Sheets(1).Range("a" & ThisWorkbook.Sheets(1).UsedRange.Rows.Count + 1)
            Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
            ActiveWorkbook.Close False
            
            End If
            
            Next
            'Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
        Set sht = Nothing
        Set fol = Nothing
        Set fs = Nothing
        Set f = Nothing
        ActiveSheet.UsedRange.Rows.AutoFit
        
        MsgBox "It's Done"
        
    End Sub
    Above code working fine, issue data append copied data to the last used row

    Code used to copy data from multiple workbooks from one folder and with the same sheet name
    One folder
    Multiple workbooks
    Same sheet name in all workbooks
    Issue :
    Should start from ROW1
    Consolidate into new workbook showing gaap between data copied from multiple sheets

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: append copied data to the last used row

    Change this
    wbk.Sheets(shtname).Rows("1:" & Sheets(shtname).UsedRange.Rows.Count).Copy _
     ThisWorkbook.Sheets(1).Range("a" & ThisWorkbook.Sheets(1).UsedRange.Rows.Count + 1)
    To this
    If Application.CountA(ThisWorkbook.Sheets(1).Rows(1) = 0 Then
         wbk.Sheets(shtname).Rows("1:" & Sheets(shtname).UsedRange.Rows.Count).Copy _
            ThisWorkbook.Sheets(1).Range("A1")
    Else
         wbk.Sheets(shtname).Rows("1:" & Sheets(shtname).UsedRange.Rows.Count).Copy _
            ThisWorkbook.Sheets(1).Range("a" & ThisWorkbook.Sheets(1).UsedRange.Rows.Count + 2)
    End If
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Hyderbad
    MS-Off Ver
    Excel 2003
    Posts
    123

    Re: append copied data to the last used row

    Hi

    compile error.
    Expected list separator or ) at this line :
    If Application.CountA(ThisWorkbook.Sheets(1).Rows(1) = 0 Then


    .Attachment 696334

  4. #4
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: append copied data to the last used row

    If Application.CountA(ThisWorkbook.Sheets(1).Rows(1)) = 0 Then
    Needed one more Parenthesis.

  5. #5
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Hyderbad
    MS-Off Ver
    Excel 2003
    Posts
    123

    Re: append copied data to the last used row

    Hi,

    Still showing blank rows.

    attached sample excel sheets


    Inventory1 - Sheet1
    Inventory2 - Sheet1

    consilidate into
    Consht(code in module) = Sheet1 + Sheet1
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    06-27-2010
    Location
    sYRIA
    MS-Off Ver
    Excel 2013
    Posts
    669

    Re: append copied data to the last used row

    deleted ?????
    Last edited by mohadin; 09-23-2020 at 03:30 AM.

  7. #7
    Valued Forum Contributor
    Join Date
    06-27-2010
    Location
    sYRIA
    MS-Off Ver
    Excel 2013
    Posts
    669

    Re: append copied data to the last used row

    Hi
    Simply

    HTML Code: 
    Last edited by mohadin; 09-23-2020 at 03:30 AM.

  8. #8
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: append copied data to the last used row

    See what this does.

    Sub ConsolidateCommSheetFromAllWbk()
        Dim folpath As String, wbk As Workbook, shtname As String
        Dim fs, fol As Object, f
            With Application.FileDialog(msoFileDialogFolderPicker)
                .Show
                folpath = .SelectedItems(1)
            End With
        Set fs = CreateObject("scripting.filesystemobject")
        Set fol = fs.getfolder(folpath)
        Sheets(1).Cells.Clear
        'Sheets(1).Cells(1, 1) = "Heading-1"
        shtname = InputBox("Enter SheetName")
            For Each f In fol.Files
                If UCase(fs.getextensionname(f.Name)) = "XLSX" Or _
                    UCase(fs.getextensionname(f.Name)) = "XLS" Then
                    Set wbk = Workbooks.Open(f.Path)
                    If Application.CountA(ThisWorkbook.Sheets(1).Rows(1)) = 0 Then
                        wbk.Sheets(shtname).UsedRange.Copy _
                        ThisWorkbook.Sheets(1).Range("A1")
                    Else
                        wbk.Sheets(shtname).UsedRange.Copy _
                        ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp)(3)
                    End If
                    'Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
                    wbk.Close False
                End If
            Next
            'Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
        Set sht = Nothing
        Set fol = Nothing
        Set fs = Nothing
        Set f = Nothing
        ActiveSheet.UsedRange.Rows.AutoFit
        MsgBox "It's Done"
    End Sub
    Last edited by JLGWhiz; 09-23-2020 at 02:33 PM.

  9. #9
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Hyderbad
    MS-Off Ver
    Excel 2003
    Posts
    123

    Re: append copied data to the last used row

    Hi, Please have a look at below snap..Blank row @506 (blank row, consolidate between the two sheets)


    BlankRow1.PNG
    BlankRow.PNG
    Last edited by jagadeesh.rt; 09-24-2020 at 08:48 AM. Reason: Adding text and snap

  10. #10
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: append copied data to the last used row

    Issue :
    Should start from ROW1
    Consolidate into new workbook showing gaap between data copied from multiple sheets
    Does this not mean that you want a blank row between copied sheets? Also, your consolidated file in your link shows a blank row between copied files. Please be clear on what you expect to see in the consolidated sheet. If you do not want a blank row, then make that statemen clear.
    Last edited by JLGWhiz; 09-24-2020 at 09:51 AM.

  11. #11
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Hyderbad
    MS-Off Ver
    Excel 2003
    Posts
    123

    Re: append copied data to the last used row

    No blank rows required in consolidated sheet, copied data should be continued...........from row1
    =====================================
    Does this not mean that you want a blank row between copied sheets?
    =====================================

    No blanks rows in consolidated sheet at all, copied data from row1

  12. #12
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: append copied data to the last used row

    This should place the copied sheets on the next row without a blank row between.

    Sub ConsolidateCommSheetFromAllWbk()
        Dim folpath As String, wbk As Workbook, shtname As String
        Dim fs, fol As Object, f
            With Application.FileDialog(msoFileDialogFolderPicker)
                .Show
                folpath = .SelectedItems(1)
            End With
        Set fs = CreateObject("scripting.filesystemobject")
        Set fol = fs.getfolder(folpath)
        Sheets(1).Cells.Clear
        'Sheets(1).Cells(1, 1) = "Heading-1"
        shtname = InputBox("Enter SheetName")
            For Each f In fol.Files
                If UCase(fs.getextensionname(f.Name)) = "XLSX" Or _
                    UCase(fs.getextensionname(f.Name)) = "XLS" Then
                    Set wbk = Workbooks.Open(f.Path)
                    If Application.CountA(wbk.Sheets(shtnme).UsedRange) <> 0 Then
                        If Application.CountA(ThisWorkbook.Sheets(1).Rows(1)) = 0 Then
                            wbk.Sheets(shtname).UsedRange.Copy _
                            ThisWorkbook.Sheets(1).Range("A1")
                        Else
                            wbk.Sheets(shtname).UsedRange.Copy _
                            ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp)(2)
                        End If
                    End If
                    'Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
                    wbk.Close False
                End If
            Next
            'Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
        Set sht = Nothing
        Set fol = Nothing
        Set fs = Nothing
        Set f = Nothing
        ActiveSheet.UsedRange.Rows.AutoFit
        MsgBox "It's Done"
    End Sub

  13. #13
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Hyderbad
    MS-Off Ver
    Excel 2003
    Posts
    123

    Re: append copied data to the last used row

    Hi JLGWhiz,

    Perfectly worked. Thank You
    If Application.CountA(wbk.Sheets(shtname).UsedRange) <> 0 Then

    Add on requirement to the existing one
    Instead of copying entire data, need to find product number containing text CA (wildcards-column c)

    Sample sheet attached.
    ==========
    In addition to the above, small requirement to the existing one,
    In given sheet name (shtname = InputBox("Enter SheetName")
    A. Open workbook – given sheetname – column c(PRODUCT NUMBER) – find CA (wildcard) – autofilter and
    B. Usedrange.copy and copy to the destination sheet.
    ==========
    Attached Files Attached Files

  14. #14
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: append copied data to the last used row

    You need to start a new thread for that, since it is beyond the scope of the original issue.
    Regards, JLG

  15. #15
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Hyderbad
    MS-Off Ver
    Excel 2003
    Posts
    123

    Re: append copied data to the last used row

    Thanks JLG,
    For the post,append copied data to the last used row

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 05-01-2018, 05:15 PM
  2. Change Code to Append Copied Data
    By singerbatfink in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-18-2016, 10:10 AM
  3. [SOLVED] Attemting to take a column of data and append it to multiple rows of data on another sheet
    By JamesJohnson31 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-17-2016, 10:34 AM
  4. [SOLVED] When data is copied to another workbook, hyperlinks as not copied or they don't work
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-17-2014, 06:46 AM
  5. Copying multiple worksheet so that copied hyperlinks link to new copied sheets
    By chrisjames25 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-01-2013, 02:37 PM
  6. [SOLVED] Move Data to another Sheet append to bottom of list and create a CSV of data
    By thequiff in forum Excel Programming / VBA / Macros
    Replies: 41
    Last Post: 07-26-2012, 06:38 AM
  7. Replies: 0
    Last Post: 07-18-2012, 03:45 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1