Change the range in this code line'Fill in the range that you want to copy Set Copy Rng = sh. I use A: A to copy the whole column but you can also use a range like A1: A10. Change it here 'Fill in the column(s) that you want to copy Copy Rng.
I have a few dozen excel files which are all of the same format (i.e. I need to combine all the files into 1 master file which must have just 2 of the 4 worksheets.
Open the two Excel worksheets containing the data you want to consolidate.
They might or might not be part of the same workbook. Start by clicking your mouse in the “Reference” field within the Function box.
Note that the lookup is still done in the first column.
Next, select a rectangle in Sheet 3 starting at A1 and having the size of the data in Sheet1 (same number of rows and columns).Where do I paste the code that I find on the internet You can also find the information from this page in this article on MSDN: Consolidating Data from Multiple Worksheets into a Summary Worksheet in Excel a zip file with a workbook with the code from this page to test the examples. Download Merge Worksheets Note: This example use the function Last Row This example copy the range A1: G1 from each worksheet. Cut Copy Mode = False End With End If End If Next Exit The Sub: Application. Cells(1) Note: This example use the function Last Col This example copy column A from each sheet after the last column with data on the Dest Sh. In the examples I use a values/formats copy but below the first example there is example code to copy only the values or everything to the RDBMerge Sheet. Enable Events = True End With End Sub Function Last Row(sh As Worksheet) On Error Resume Next Last Row = sh. Row On Error Go To 0 End Function Function Last Col(sh As Worksheet) On Error Resume Next Last Col = sh. Important: The macro examples use the Last Row or Last Col function that you can find in the last section of this page.