CODEWITHSUNDEEP
vbaexcel
SGTScottAdams
SGTScottAdams

Reputation: 11

Copy multiple worksheets into one

I have spent a while searching for help on writing code to copy matching data from multiple worksheets into a single sheet, but the only one that did something similar that I found was located here: Find Match, Copy Row from Sheet1 and Insert Into Sheet2

I tried adapting it to fit my needs, but I am not very good at VBA and I could not get it to do what I want. Any help would be greatly appreciated.

What I have is a workbook that has four worksheets; Master, FescoMonthly, Schedule, and FescoData. These worksheets are sent to us by our customers so I can import them, but I do not want to change their layouts, which is part of the problem. I want the macro to search for matching meter numbers in every worksheet and copy certain row to a new worksheet called Combined.

In the Master worksheet I need to copy the whole thing from row 5 to bottom (currently 1982 but more could be added) and columns A through F. -----Column A is meter number

In the FescoMonthly worksheet I need to copy row 5 to bottom and column A through H by matching identical meter numbers from column D (meter number) to column A of the main worksheet.

In the Schedule worksheet I need to copy row 5 to bottom and column A through D by matching column A (meter number) to column A of the main worksheet.

In the FescoData worksheet I need to copy row 5 to bottom and column A through C by matching column A (meter number) to column A of the main worksheet.

If someone could please help me with just matching and copying Master and FescoMonthly I think I could figure out how to do the others and I could repost my work to see if I am doing it right.

Upvotes: 1

Views: 1890

Answers (1)

SeanC
SeanC

Reputation: 15923

set sht to the worksheet that has the data you want to copy
set trg to the target worksheet
set colCount to the number of columns to copy 

Set rng = sht.Range(sht.Cells(5, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount)) 
' start at row 5, then get the last row with data, and then expand to the  number of columns
trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value 
' find last unused cell in first column, and copy the data there

(use 1048576 instead of 65536 in excel 2007 or later)

Upvotes: 0

Related Questions