multiple worksheet copy and paste formatting question

[hamroll]

Hi,

I am using the following script in Excel 2003:

Sub Combine()
	Dim J As Integer

	On Error Resume Next
	Sheets(1).Select
	Worksheets.Add ' add a sheet in first place
	Sheets(1).Name = "Combined"

	' copy headings
	Sheets(2).Activate
	Range("A1").Cells.Select
	Selection.Copy Destination:=Sheets(1).Range("A1")

	' work through sheets
	For J = 2 To Sheets.Count ' from sheet 2 to last sheet
		Sheets(J).Activate ' make the sheet active
		Range("A1").Select
		Selection.CurrentRegion.Select ' select all cells in this sheets

		' select all lines except title
		Selection.Offset(1, 0).Resize(Selection.Rows.Count - 82, Selection.Columns.Count - 8).Select
		
		' copy cells selected in the new sheet on last line
		Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp).Offset(1, 0)(2)
		
	   
	Next
End Sub

It has been changed slightly from where I found it on the web - sorry I can reference the site, I just closed the tab!!!!

Any way I've managed to change it to meet my requirements except for two things. Currently this script will collect the first 20 records from the first column from every worksheet and bring them into one worksheet. When it does this it copies each set of records underneath each other (on the last row).

How can I paste the records horizontally instead (on the last column instead)?

Also is there an easy was to auto resize the columns to fit the content (which differs in size)?

Thanks in advance for any help anyone can give me...I'm a total novice with VB and don't have a clue really.

 

[hamroll]

sorry for the bump, if anyone can help, I would really appreciate it