+ Reply to Thread
Results 1 to 10 of 10

Macro to Copy Data from Multiple Sheets Into One

Hybrid View

Cardan Macro to Copy Data from... 05-12-2021, 06:14 PM
cubangt Re: Macro to Copy Data from... 05-12-2021, 06:47 PM
Cardan Re: Macro to Copy Data from... 05-13-2021, 10:43 AM
Marc L Try this ! 05-12-2021, 07:09 PM
Cardan Re: Try this ! 05-13-2021, 10:40 AM
Marc L Re: Macro to Copy Data from... 05-13-2021, 12:06 PM
Cardan Re: Macro to Copy Data from... 05-13-2021, 01:40 PM
Marc L Re: Macro to Copy Data from... 05-13-2021, 05:17 PM
Cardan Re: Macro to Copy Data from... 05-17-2021, 01:10 PM
cubangt Re: Macro to Copy Data from... 05-13-2021, 05:35 PM
  1. #1
    Registered User
    Join Date
    07-26-2006
    MS-Off Ver
    Office 365
    Posts
    97

    Macro to Copy Data from Multiple Sheets Into One

    I have a model that needs a macro to copy data in other worksheets into one sheet. The good thing is each sheet has the same rules for starting cell and number of columns. The issue is that the number or rows will differ in each worksheet.
    Here is how I am envisioning the setup. (see attachment as well)
    1. The worksheet data needs to be copied into is called “Combined”
    2. The sheets that need copying are between what I call “bookends”
    a. The bookends are Sheet “B” (for beginning) and Sheet “E” (End)
    3. The macro will look in the first sheet after (to the right of) Sheet “B”
    4. It will identify the range of data. The range will always start in cell A10 and pull the columns A through K
    5. It will determine the range (rows down) by finding the first “blank” cell in column A (and not include the blank row).
    a. For example, if the first blank after A10 is A20, the range selected will be A10:K19
    6. The macro will copy all the data (and formatting) and place it starting in A10 of the “Combined” tab.
    7. The macro will repeat this for the next tab between the “B” & “E” bookends, copy and paste the new data range directly under the last data set in the “Combined” tab
    8. The macro will repeat this process until it gets to the “E” tab and then ends.
    I’m hoping the steps above are systematic enough. Sometimes I can be vague and my intent unclear. If unclear please respond and I will clarify.
    Thank you in advance for your time.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    901

    Re: Macro to Copy Data from Multiple Sheets Into One

    It is all possible and I'm currently doing the same thing, but I'm not at work where I have my file to provide you the code.
    I have subscribed to this post and will assist tomorrow if no one else chimes in.

    Its pretty simple in a way, but unfortunately I cant provide sample code until tomorrow..
    If you find the suggestion or solution helpful, please consider adding reputation to the post.

  3. #3
    Registered User
    Join Date
    07-26-2006
    MS-Off Ver
    Office 365
    Posts
    97

    Re: Macro to Copy Data from Multiple Sheets Into One

    Thank you cubangt. I just replied to Marc L's response. His elegant macro gets my objective to ~90%. It just needs to specify ranges per each sheet. Another sample couldn't hurt. I am on the learning curve so any other examples are helpful.

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this !


    As this demonstration works only with good enough readers …

    According to the attachment a VBA demonstration as a beginner starter to paste to the Combined worksheet module :

    PHP Code: 
    Sub Demo1()
            
    Application.ScreenUpdating False
        
    For S& = Sheets("B").Index 1 To Sheets("E").Index 1
            With Sheets
    (S).[A9].CurrentRegion.Rows
                
    If .Count 1 Then .Item("2:" & .Count).Copy Cells(Rows.Count1).End(xlUp)(2)
            
    End With
        Next
            Application
    .ScreenUpdating True
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 05-12-2021 at 07:22 PM. Reason: optimization …

  5. #5
    Registered User
    Join Date
    07-26-2006
    MS-Off Ver
    Office 365
    Posts
    97

    Re: Try this !

    Thank you for the response Marc. I love the simplicity of this macro.
    There are a couple issues as it stands.
    1. Each sheet has information above and below the range that I need to copy over. I am just trying to grab the data range that starts in A10 and goes to K??
    2. When copying over, I need it to start copying in cell A10 on the Combined tab as well. The Combined tab will ultimately have info/headers in rows 1-9
    3. Also, the price column on the sheets (column I) sometimes has formulas and may have links. I would like to make sure any links or formulas are removed before copying over.

    Thank you again. I'm in awe of the how much this macro does with so few lines!

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Macro to Copy Data from Multiple Sheets Into One


    As I wrote « According to the attachment » so try my demonstration with it and see how it well rocks !

  7. #7
    Registered User
    Join Date
    07-26-2006
    MS-Off Ver
    Office 365
    Posts
    97

    Re: Macro to Copy Data from Multiple Sheets Into One

    ah, I see. So this issue is interesting-and above my skill level.
    It works splendidly in the slimmed down workbook I attached, but when I add it to my working file, it does the following:
    1. In my working file, the macro starts the range to copy in A2 rather than A10.
    2. It does not copy any of the text below the last value in the data range - which is good!
    3. The Price column (and the Phase column) still comes over as formulas if the input is a formula. It would be preferred any formulas would be overwritten.

    Unsure of the discrepancy. My working file has the same values/format in A1:A9. And I can't seem to find a quick change in the code. Is it a simple fix?

    Also, not sure if it matters, my goal will be to create a button in the Combined tab and assign the macro to it, so all I have to do is hit a button whenever I need to populate/repopulate the data.

    Thank you again for your assistance.

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: Macro to Copy Data from Multiple Sheets Into One


    For your first point you can add the headers in row #9 before to launch the VBA procedure.
    It's what happens when the attachment does not well reflect the real workbook …

    For your last point, it seems you need the slowest way :
    no formula - this important information must be in the initial post ! - but still keeping the cells formatting ?

  9. #9
    Registered User
    Join Date
    07-26-2006
    MS-Off Ver
    Office 365
    Posts
    97

    Re: Macro to Copy Data from Multiple Sheets Into One

    Marc, You are absolutely correct. In my attempts to scale down my workbook for sending, I did overlook a few steps which is on me. Thank you for your input. I got the macro working and it is excellent.

    Thank you again.

  10. #10
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    901

    Re: Macro to Copy Data from Multiple Sheets Into One

    Couldnt the values be pasted for #3 as
    .Range(XX).PasteSpecial xlPasteValues so that the sheets only have the results of all the formulas?

+ 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. [SOLVED] Macro to copy data from multiple sheets and put it in one sheet.......everyday
    By Ash_Maverick in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-01-2016, 05:20 AM
  2. [SOLVED] Copy data from multiple sheets to one sheet macro
    By sinapourazima in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-13-2014, 12:49 AM
  3. Creating a copy/paste Macro with data from multiple sheets
    By sierradk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-12-2014, 05:21 PM
  4. Help with creating a macro that will copy and paste data to multiple sheets.
    By harkin123 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 08-19-2013, 06:57 AM
  5. Replies: 1
    Last Post: 02-08-2013, 10:36 PM
  6. [SOLVED] Macro to copy data from multiple sheets to one (new) master sheet
    By nhtodd in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-24-2012, 02:30 AM
  7. Macro to Copy Data from One Sheet onto Multiple Sheets
    By ssteines in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 03-03-2011, 05:50 PM

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