Results 1 to 3 of 3

Thread: Copy data from one sheet to another sheet

  1. #1
    Junior Member
    Join Date
    Mar 2017
    Posts
    14
    Rep Power
    0

    Post Copy data from one sheet to another sheet

    Hi,

    i have a sheet 1 and sheet 2 have a data with formula column from A to I its starts from row# 5 to 108. Now all the data from sheet 1 and sheet 2 need to copy and paste ( join with workbook) to sheet 3 by clicking button Get data in sheet 3.

    The link of cross post :-

    http://chandoo.org/forum/threads/tra...pasting.33575/

    Marc L has almost done working with code but issue is not copying with next empty row.

    Eg:- sheet 1 A5 to Ai from rows 5 to 108 the data from A5 to A9
    sheet 2 A5 to Ai from rows 5 to 108 the data from A5 to A7
    but sheet 3 copy whole rows from 5 to 108 in sheet 1 and having empty spaces and copy sheet 2 rows from 5 to 108.

    i don't want the empty rows in sheet 3 between both sheets i need the data contentiously copy to next empty row.

    if possible experts can u check with code what wrong on it or can change the code with better solution

    Code:
    Sub Button13_Click()
        Sheet2.UsedRange.Offset(4).Clear
        Sheet1.[A5:I5].Resize(Sheet1.Cells(Rows.Count, 1).End(xlUp).Row - 4).Copy Sheet2.[A5]
        Sheet4.[A5:I5].Resize(Sheet4.Cells(Rows.Count, 1).End(xlUp).Row - 4).Copy Sheet2.Cells(Rows.Count, 1).End(xlUp)(2)
    End Sub
    Attached Files Attached Files

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Welcome to ExcelFox!

    try this

    Code:
        Dim rngLCell    As Range
        
        Sheet2.UsedRange.Offset(4).Clear
            
        Set rngLCell = Sheet1.Cells.Find(What:="*", After:=Sheet1.Range("c1"), LookIn:=xlValues, LookAt:= _
                                xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
        If rngLCell.Row > 5 Then
            Sheet1.Range("a5:i" & rngLCell.Row).Copy Sheet2.Range("a5")
        End If
        
        Set rngLCell = Nothing
        Set rngLCell = Sheet4.Cells.Find(What:="*", After:=Sheet4.Range("c1"), LookIn:=xlValues, LookAt:= _
                                xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
        If rngLCell.Row > 5 Then
            Sheet4.Range("a5:i" & rngLCell.Row).Copy Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp)(2)
        End If
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Mar 2017
    Posts
    14
    Rep Power
    0
    hi admin,

    your quick response i really heartfelt appreciate.
    Excellent work perfect , thank a lot , thank u so much ...

Similar Threads

  1. copy data and paste it in another sheet
    By newbie2 in forum Excel Help
    Replies: 1
    Last Post: 07-15-2015, 06:21 PM
  2. Replies: 2
    Last Post: 09-30-2013, 03:40 PM
  3. Replies: 1
    Last Post: 05-19-2013, 02:37 PM
  4. Replies: 1
    Last Post: 02-10-2013, 06:21 PM
  5. Replies: 2
    Last Post: 12-26-2012, 08:31 AM

Posting Permissions

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