Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Macro - To copy/append sheets to new workbook

  1. #1

    Macro - To copy/append sheets to new workbook



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

    Hi If anyone please help.

    I have two sheets
    1. One is source and other is master.
    2.Master sheet have 10 sheets and source have 7 sheets.
    3.Source file with 7 sheets are updated monthly and need to be pasted to master sheets(same name 7 sheets on master ).
    4. While pasting data from source to master, data on each sheet should be pasted without headers and in the end of the master sheet.(e.g sheet A have 10 rows then paste should be from a11)

    Can any one please help me to write the code. I 've attached the sample xls, in these 1,2,3,4 from source sheet should be appended over to 1,2,3,4 of the master sheet excluding headers.

    Thanks.
    Attached Files Attached Files

  2. #2
    Can any one please help.

    Thanks

    Quote Originally Posted by mightyg View Post
    Hi If anyone please help.

    I have two sheets
    1. One is source and other is master.
    2.Master sheet have 10 sheets and source have 7 sheets.
    3.Source file with 7 sheets are updated monthly and need to be pasted to master sheets(same name 7 sheets on master ).
    4. While pasting data from source to master, data on each sheet should be pasted without headers and in the end of the master sheet.(e.g sheet A have 10 rows then paste should be from a11)

    Can any one please help me to write the code. I 've attached the sample xls, in these 1,2,3,4 from source sheet should be appended over to 1,2,3,4 of the master sheet excluding headers.

    Thanks.

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by mightyg View Post
    Can any one please help me to write the code.
    By which you mean
    Quote Originally Posted by mightyg View Post
    Can any one write the code.
    Code:
    Sub blah()
    For Each sht In Workbooks("source2.xlsm").Sheets
      With Workbooks("Master.xlsm").Sheets(sht.Name)
        Set rngToCopy = sht.UsedRange
        rngToCopy.Resize(rngToCopy.Rows.Count - 1).Offset(1).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
      End With
    Next sht
    End Sub
    Both workbooks need to be open.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    Oh groan!
    You've cross posted: http://forum.chandoo.org/threads/mov...s-macro.25512/

    myightyg, for your information, you should always provide links to your cross posts.
    If you have cross posted at other places, please add links to them too.
    Why? Have a read of http://www.excelguru.ca/content.php?184

  5. #5
    this is working as required thanks alot for your help.

  6. #6
    Hi just wondering is it possible to add some criteria if this macro is run twice then rows will be pasted twice. Can we add some condition to restrict the copy/paste if the date data(which is always column A) is already there.

    For example I have already run the macro for Jan2014 then there will already a record for jan2014 and if I run the macro again there will be a duplicate row. Column A is just 'YearMonth' format and is unique value

    Thanks

  7. #7
    Hi P45cal can you please reply thanks

  8. #8

  9. #9
    Quote Originally Posted by snb View Post
    Not sure why you have pasted my threads link????

  10. #10
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    375
    Articles
    0
    Excel Version
    2020
    Because apparently you did not read #4

Page 1 of 2 1 2 LastLast

Posting Permissions

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