This macro to combine sheets omits some data, is it because of usedrange?

[sheetspread]

Excel 2003
	A	B	C	D	E	F
1	Name	Number	Nextnumber
2	L	995	942
3	M	964	32
4	N	267	356
5	O	849	463
6	P	930	899
7	Q	626	918
8	R	605	845
9	S	60	887
10	T	485	605
11	U	287	167
12	V	590	374
13	W	998	885
14	X	835	116
15	Y	589	958
16	Z	396	236
17
18			8683
19					Extra	4343
First

Cell Formulas
Range	Formula
C18	=SUM(C2:C17)

Excel 2003
	A	B	C	D	E	F	G	H	I
1	Name	Number	Nextnumber
2	A	290	936
3	B	612	997
4	H	510	937					Post	455
5	I	935	928
6	J	908	312
7	K	395	596
8	L	995	942
9	M	964	32
10	N	267	356
11	O	849	463
12	P	930	899
13	Q	626	918
14	R	605	845
15	S	60	887
16	T	485	605
17	U	287	167
18	V	590	374
19	W	998	885
20
21			12079
Second

Cell Formulas
Range	Formula
C21	=SUM(C2:C20)

Excel 2003
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Number	Nextnumber
2	A	290	936
3	B	612	997
4	C	49	657
5	D	743	652
6	E	658	526
7	F	720	117
8	G	521	272
9	R	605	845
10	S	60	887
11	T	485	605
12	U	287	167
13	V	590	374
14	W	998	885
15	X	835	116
16	Y	589	958
17	Z	396	236
18
19			9230
20
21
22
23
24						Dont change	766
25
26
27
28
29
30												344
Third

Cell Formulas
Range	Formula
C19	=SUM(C2:C18)

Sub allsheetstoone()
Dim i As Integer
Worksheets.Add.Name = "Master"
For i = 1 To Sheets.Count
If Sheets(i).Name <> "Master" Then
Sheets(i).UsedRange.Copy Destination:=Sheets("Master").Range("A" & Sheets("Master").UsedRange.Rows.Count)
End If
Next
End Sub

Excel 2003
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Number	Nextnumber
2	L	995	942
3	M	964	32
4	N	267	356
5	O	849	463
6	P	930	899
7	Q	626	918
8	R	605	845
9	S	60	887
10	T	485	605
11	U	287	167
12	V	590	374
13	W	998	885
14	X	835	116
15	Y	589	958
16	Z	396	236
17
18			8683
19	Name	Number	Nextnumber
20	A	290	936
21	B	612	997
22	H	510	937					Post	455
23	I	935	928
24	J	908	312
25	K	395	596
26	L	995	942
27	M	964	32
28	N	267	356
29	O	849	463
30	P	930	899
31	Q	626	918
32	R	605	845
33	S	60	887
34	T	485	605
35	U	287	167
36	V	590	374
37	W	998	885
38
39	Name	Number	Nextnumber
40	A	290	936
41	B	612	997
42	C	49	657
43	D	743	652
44	E	658	526
45	F	720	117
46	G	521	272
47	R	605	845
48	S	60	887
49	T	485	605
50	U	287	167
51	V	590	374
52	W	998	885
53	X	835	116
54	Y	589	958
55	Z	396	236
56
57			9230
58
59
60
61
62						Dont change	766
63
64
65
66
67
68												344
Master

Cell Formulas
Range	Formula
C18	=SUM(C2:C17)
C57	=SUM(C40:C56)

As you can see Row 19 from the First tab is missing/pasted over. I'm using usedrange.rows.count as the destination but rows.count+1 and rows.count.offset(1,0) (along with many other variations) haven't worked either.

So how can I vertically paste each sheet into one without omissions?


After someone solves this I'll work on preventing the extra headers and always putting the Master sheet first.

 

[VoG]

Assuming no filters in place try

Sub allsheetstoone()
Dim i As Integer
Worksheets.Add.Name = "Master"
For i = 1 To Sheets.Count
If Sheets(i).Name <> "Master" Then
    Sheets(i).UsedRange.Copy Destination:=Sheets("Master").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next
End Sub

 

[sheetspread]

Thanks but that chops off another row:

Excel 2003
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Name	Number	Nextnumber
3	L	995	942
4	M	964	32
5	N	267	356
6	O	849	463
7	P	930	899
8	Q	626	918
9	R	605	845
10	S	60	887
11	T	485	605
12	U	287	167
13	V	590	374
14	W	998	885
15	X	835	116
16	Y	589	958
17	Z	396	236
18	Name	Number	Nextnumber
19	A	290	936
20	B	612	997
21	H	510	937					Post	455
22	I	935	928
23	J	908	312
24	K	395	596
25	L	995	942
26	M	964	32
27	N	267	356
28	O	849	463
29	P	930	899
30	Q	626	918
31	R	605	845
32	S	60	887
33	T	485	605
34	U	287	167
35	V	590	374
36	W	998	885
37	Name	Number	Nextnumber
38	A	290	936
39	B	612	997
40	C	49	657
41	D	743	652
42	E	658	526
43	F	720	117
44	G	521	272
45	R	605	845
46	S	60	887
47	T	485	605
48	U	287	167
49	V	590	374
50	W	998	885
51	X	835	116
52	Y	589	958
53	Z	396	236
54
55			9230
56
57
58
59
60						Dont change	766
61
62
63
64
65
66												344
Master

Cell Formulas
Range	Formula
C55	=SUM(C38:C54)

 

[VoG]

Try

Sub allsheetstoone()
Dim i As Integer
Worksheets.Add.Name = "Master"
For i = 1 To Sheets.Count
If Sheets(i).Name <> "Master" Then
    Sheets(i).UsedRange.Copy Destination:=Sheets("Master").Range("C" & Rows.Count).End(xlUp).Offset(1, -2)
End If
Next
End Sub

 

[sheetspread]

Not yet:

Excel 2003
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Name	Number	Nextnumber
3	L	995	942
4	M	964	32
5	N	267	356
6	O	849	463
7	P	930	899
8	Q	626	918
9	R	605	845
10	S	60	887
11	T	485	605
12	U	287	167
13	V	590	374
14	W	998	885
15	X	835	116
16	Y	589	958
17	Z	396	236
18
19			8683
20	Name	Number	Nextnumber
21	A	290	936
22	B	612	997
23	H	510	937					Post	455
24	I	935	928
25	J	908	312
26	K	395	596
27	L	995	942
28	M	964	32
29	N	267	356
30	O	849	463
31	P	930	899
32	Q	626	918
33	R	605	845
34	S	60	887
35	T	485	605
36	U	287	167
37	V	590	374
38	W	998	885
39
40			12079
41	Name	Number	Nextnumber
42	A	290	936
43	B	612	997
44	C	49	657
45	D	743	652
46	E	658	526
47	F	720	117
48	G	521	272
49	R	605	845
50	S	60	887
51	T	485	605
52	U	287	167
53	V	590	374
54	W	998	885
55	X	835	116
56	Y	589	958
57	Z	396	236
58
59			9230
60
61
62
63
64						Dont change	766
65
66
67
68
69
70												344
Master

Cell Formulas
Range	Formula
C19	=SUM(C3:C18)
C40	=SUM(C21:C39)
C59	=SUM(C42:C58)

This is the first code I've written that requires the last nonblank row rather than cell, combining LR and LC, and of course it's a lot harder than I hoped.

 

[sheetspread]

In Sheet "First" it's F which isn't printing but in Sheet "Second" it's I which is and "Third"'s L is also.

 

[VoG]

Try

Sub allsheetstoone()
Dim i As Integer
Worksheets.Add.Name = "Master"
For i = 1 To Sheets.Count
If Sheets(i).Name <> "Master" Then
    LR = Sheets("Master").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Sheets(i).UsedRange.Copy Destination:=Sheets("Master").Range("A" & LR + 1)
End If
Next
End Sub

 

[sheetspread]

"Run Time Error 91: Object variable or with block variable not set"

LR = Sheets("Master").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

 

[VoG]

I will write out 100 lines "VoG is a pillock"

Sub allsheetstoone()
Dim i As Long, LR As Long
Worksheets.Add.Name = "Master"
For i = 1 To Sheets.Count
    If Sheets(i).Name <> "Master" Then
        With Sheets("Master")
            LR = 1
            On Error Resume Next
            LR = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
            On Error GoTo 0
            Sheets(i).UsedRange.Copy Destination:=.Range("A" & LR + 1)
        End With
    End If
Next i
End Sub

 

[sheetspread]

More like a genius. Thanks, this is perfect.