Help Building Macro that Copies the same range from multiple sheets into a summary tab

[dbirt31]

Hello all,

I'm looking for some help after spending the last two days searching the threads for a modifiable macro to solve my current problem, and unfortunately finding myself unsuccessful.

Scenario:

I am using a different macro to copy in files from a folder into one workbook on different sheets. Once these sheets are in the workbook (300-500 sheets), I'd like to copy and paste value a range, AN1:AY111 onto a summary page (Row 1 is headers and do not to be copied again after first sheet. If this is an issue we can just do AN2:Ay111 and I can manually insert the headings later) As a result, I'd like to have one very long summary sheet with all of this data at the front of the workbook. The other tabs should remain untouched. I am using Excel 2013

I have been so close to creating one from previous posts but ran into a lot of trouble with making sure the data pasted was Paste Special - Value (Data contains formulas, so all I want is the value).

Bonus points if you can combine the two macros to insert all files from one folder (one tabbed files) into one workbook and then combine the data from the worksheets into a summary tab as mentioned above.

 

[jeffreybrown]

Hello & Welcome to the Forum,

This should help you with the merging of all worksheets into a summary tab.

Merge cells from all or some worksheets into one Master sheet

As far as bonus points, afraid I'm out there; although, you could just add a Call Statement to the end of the first macro to call the second macro

 

[dbirt31]

I found that one before and was one of the issues I was having as it seems to have a bug? For some reason whenever I run that Macro the following lines come up as a "Compile Error: Sub or Function not Defined."

and highlights "LastRow"
'Find the last row with data on the DestSh
Last = LastRow(DestSh)

 

[jeffreybrown]

I found that one before and was one of the issues I was having as it seems to have a bug?

I along with many other individuals have used this macro by Ron before and not had any problems.

Did you look at the Example Workbook?

Also, did you paste the Common Functions required for all routines from the bottom of the page?

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

Function LastCol(sh As Worksheet)
    On Error Resume Next
    LastCol = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
    On Error GoTo 0
End Function

 

[dbirt31]

That was probably the simplest error i could have had. Thanks for helping me out and your patience, really appreciate it. Wish I knew that was the simple fix on Monday..

 

[jeffreybrown]

You're welcome and thanks for the feedback.