Joel, everything worked perfectly...
--
thanks in advance
"Joel" wrote:
> The code below makes a copy of the yr2005 worksheet and calls the new sheet
> summary. Then it goes through the yr2007 worksheet and checks if an entry
> exists or doesn't exist for each dept-job. If it exists it puts the 07 data
> in columns F-H. Otherwise it adds a new row, fills in columns A and b with
> dept-job, and puts the data in columns F-H.
>
>
>
> Sub combine()
>
> 'copy 05 worksheet
> Worksheets("yr2005").Copy after:=Worksheets(Sheets.Count)
> With ActiveSheet
> .Name = "Summary"
> SumLastRow = .Range("A" & Rows.Count).End(xlUp).Row
> SumNewRow = SumLastRow + 1
> End With
>
> With Worksheets("yr2007")
> RowCount07 = 1
> Do While .Range("A" & RowCount07) <> ""
>
> Found = False
> Dept = .Range("A" & RowCount07)
> Job = .Range("B" & RowCount07)
> Over07 = .Range("C" & RowCount07)
> Current07 = .Range("D" & RowCount07)
> Date07 = .Range("E" & RowCount07)
>
> With Sheets("Summary")
> For SumRowCount = 1 To (SumNewRow - 1)
> If .Range("A" & SumRowCount) = Dept And _
> .Range("B" & SumRowCount) = Job Then
>
> Found = True
> Exit For
> End If
> Next SumRowCount
> If Found = True Then
> .Range("F" & SumRowCount) = Over07
> .Range("G" & SumRowCount) = Current07
> .Range("H" & SumRowCount) = Date07
> Else
> .Range("A" & SumNewRow) = Dept
> .Range("B" & SumNewRow) = Job
> .Range("F" & SumNewRow) = Over07
> .Range("G" & SumNewRow) = Current07
> .Range("H" & SumNewRow) = Date07
> SumNewRow = SumNewRow + 1
> End If
> End With
> RowCount07 = RowCount07 + 1
> Loop
> End With
> End Sub
>
>
> "jcontrer" wrote:
>
> > i have two spreadsheets in the same workbook (yr2005 and yr2007), each with
> > the columns
> > a: department B: job title C: over D: current E: to date.
> >
> > one is for year 2005 and the other is for year 2007.
> > some of the the departments and positions have changed (some depts and or
> > job titles no longer exist and some are new) but most of them are the same.
> > but i need all to be in this spreadsheet without any doubles.
> >
> > WHAT I NEED IS: a macro that, takes the info on those 2 spreadsheet,
> > conjugates, and makes one, all inclusive spreadsheet with the columns a:
> > department B: job title C: over05 D: current05 E: to date05 F: over07 G:
> > current07 H: to date 07. having only one row per each departments job title
> > E.G. if its in both (yr2005 and yr 2007) spreadsheets all columns would
> > have the corresponsing values. if its a new position (didnt appear in yr2005)
> > columns c d and e would be blank. if its an old position (in yr 2005 but not
> > in 2007) columns f g and h would be blank.
> >
> >
> > --
> > thanks in advance
|
Similar Threads
