VBA to copy from one sheet to bottom of data on another sheet and re-sort

[edokhotnik]

I have two worksheets with similar data, but differing layouts. What is the way in VBA to copy certain data from Worksheet POAM to paste to the bottom of the range in Worksheet PermitRPT. Then re-sort Sheet PermitRPT alphabetically on Col A.

I have come up with formulas to do this, but would like to automate this many workbooks that have this format.

Thanks in advance for your assistance.

Ed

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Finding #	Finding	FinalRating	RequirementID/Impact	*	*	*	*	Technical	*	Status	Comments
2	1	Widget broken	I	ABCD-1/High					DG-V001103		Non-Compliant	Preliminary Rating: I Words here 1
3	2	Widget out of date	II	DEFG-1/Medium					DG-V001213		Non-Compliant	Preliminary Rating: I More Words here 2
4	3	Widget Missing	II	HIJK-2/Low					R-19385		Non-Compliant	Preliminary Rating: II Some More Words here 3
5	4	Widget painted green	III	LMNO-1/Low					RS-938		Non-Compliant	Preliminary Rating: III Even More Wordy Words here 4
6
POAM

Excel Workbook
	A	B	C	D	E	F	G
1	RequirementID	*	*	PrelimRating	Finding	Mitigation	FinalRating
2	ABCD-1			I	Non-Compliant Preliminary Rating: I DG-V001103 Widget broken	Words here 1	I
3	DEFG-1			I	Non-Compliant Preliminary Rating: I DG-V001213 Widget out of date	More Words here 2	II
4	HIJK-2			II	Non-Compliant Preliminary Rating: II R-19385 Widget Missing	Some More Words here 3	II
5	LMNO-1			III	Non-Compliant Preliminary Rating: III RS-938 Widget painted green	Even More Wordy Words here 4	III
6	ABCD-1			I	Non-Compliant Preliminary Rating: I DG-V001103 Widget broken	Words here 1	I
7	DEFG-1			I	Non-Compliant Preliminary Rating: I DG-V001213 Widget out of date	More Words here 2	II
8	HIJK-2			II	Non-Compliant Preliminary Rating: II R-19385 Widget Missing	Some More Words here 3	II
9	LMNO-1			III	Non-Compliant Preliminary Rating: III RS-938 Widget painted green	Even More Wordy Words here 4	III
Excel 2010 PermitRpt

Excel 2010

Cell Formulas
Range	Formula
D2	=MID(POAM!L2,FIND(":",POAM!L2)+2,FIND(CHAR(10),POAM!L2)-FIND(":",POAM!L2)-1)
D3	=MID(POAM!L3,FIND(":",POAM!L3)+2,FIND(CHAR(10),POAM!L3)-FIND(":",POAM!L3)-1)
D4	=MID(POAM!L4,FIND(":",POAM!L4)+2,FIND(CHAR(10),POAM!L4)-FIND(":",POAM!L4)-1)
D5	=MID(POAM!L5,FIND(":",POAM!L5)+2,FIND(CHAR(10),POAM!L5)-FIND(":",POAM!L5)-1)
E2	=POAM!K2&CHAR(10)&LEFT(POAM!L2,FIND(CHAR(10),POAM!L2))&POAM!I2&CHAR(10)&POAM!B2
E3	=POAM!K3&CHAR(10)&LEFT(POAM!L3,FIND(CHAR(10),POAM!L3))&POAM!I3&CHAR(10)&POAM!B3
E4	=POAM!K4&CHAR(10)&LEFT(POAM!L4,FIND(CHAR(10),POAM!L4))&POAM!I4&CHAR(10)&POAM!B4
E5	=POAM!K5&CHAR(10)&LEFT(POAM!L5,FIND(CHAR(10),POAM!L5))&POAM!I5&CHAR(10)&POAM!B5
F2	=MID(POAM!L2,FIND(CHAR(10),POAM!L2)+1,LEN(POAM!L2)-FIND(CHAR(10),POAM!L2)+1)
F3	=MID(POAM!L3,FIND(CHAR(10),POAM!L3)+1,LEN(POAM!L3)-FIND(CHAR(10),POAM!L3)+1)
F4	=MID(POAM!L4,FIND(CHAR(10),POAM!L4)+1,LEN(POAM!L4)-FIND(CHAR(10),POAM!L4)+1)
F5	=MID(POAM!L5,FIND(CHAR(10),POAM!L5)+1,LEN(POAM!L5)-FIND(CHAR(10),POAM!L5)+1)
G2	=POAM!C2
G3	=POAM!C3
G4	=POAM!C4
G5	=POAM!C5
A2	=LEFT(POAM!$D$2:$D$5,6)
A3	=LEFT(POAM!$D$2:$D$5,6)
A4	=LEFT(POAM!$D$2:$D$5,6)
A5	=LEFT(POAM!$D$2:$D$5,6)

 

[devthomp]

This will get your your last row, I'm pressed for time but from here you can
-> send it over to the other sheet with an sheets(sht).paste or something of that sort
-> for sorting i really don't know, try recording a macro of you sorting alphabetically

dim x as integer
dim r as integer
x = Selection.Range("A65536").End(xlUp).row
r = Cells(x, 1).Row
Rows(r).EntireRow.Copy

This code returns x as the last row of column 1 with anything in it.