Range("A1").End(xlDown).Offset(1, 0).Select

[Daburger]

I have a case/call audit sheet in which I am trying to move the data between other sheets, yet having no luck.
I keep getting the Run-time error'1004': Application-defined or object-defined error.

When I debug, it highlights the following line: Range("A1").End(xlDown).Offset(1, 0).Select

Sub add_record()

    Sheets("Temp Data").Select
    Range("A2:BJ2").Select
    Selection.Copy
    Sheets("Case-Call RAW Data").Select
    Range("A1").End(xlDown).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Case-Call Audit").Select

End Sub

Any help as always will be greatly appreciated!

 

[StephenCrump]

If you have nothing in column A, then Range("A1").End(xlDown) will be the last cell in column A, and .Offset(1) will take you off the end of the column, hence the error.

You don't need all the .Selects. Try:

Sheets("Temp Data").Range("A2:BJ2").Copy
Sheets("Case-Call RAW Data").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

 

[Crystalyzer]

Instead of

Range("A1").End(xlDown).Offset(1, 0).Select

Try

Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select

 

[Daburger]

If you have nothing in column A, then Range("A1").End(xlDown) will be the last cell in column A, and .Offset(1) will take you off the end of the column, hence the error.

You don't need all the .Selects. Try:

Sheets("Temp Data").Range("A2:BJ2").Copy
Sheets("Case-Call RAW Data").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

Instead of

Try

Thanks to both of you for your help. I really do appreciate it.
Both worked to a degree....but not completely.
Crystalyzer - I had tried this previously but forgot to mention this before. my apologies.
I will explain a little more detail below:

The story behind the macro:
I have data that I input on a sheet called "Case-Call Audit".
Think of it as just a call audit sheet. It has around 20 questions such as: did the person do XYZ?
If yes, I populate the "yes" column with a 1 for that question.
If no, I leave the "yes" column blank and populate the "no" column with a 1.
If not applicable, I leave both "yes and no" columns and populate the "N/A' column with a 1.
At the end of the audit, it calculates all the populated fields and gives a percentage which is the final score.

So the point of the macro is that it is meant to copy the data from this "Case-Call Audit" sheet and then paste it into sheet "Temp Data".
From "Temp Data", it copy's the newly pasted data and pastes it into "Case-Call RAW Data".

The "Temp Data" sheet only ever holds the most recent audit that I want to upload...nothing more.
The "Case-Call RAW Data" stores all audits that I have entered and then run the macro for.

Both your codes do work as far as adding whatever data is in the "Temp Data" sheet and then pastes it into the raw data. This is great, providing if there is data in there to copy.
The problem is I need to populate the "Temp Data" sheet from the "Case-Call Audit" first before copying any cells and pasting them into the "Case-Call Raw Data" sheet.
This is where I am currently at now.

This workbook was given to me by a friend of mine and it WAS working for him.
He has this code running for two lots of different audits, each going to their own temp data sheets and raw data sheets, etc.
One actually still works but the other one doesn't. Both use the exact same code (other than using different worksheets etc).
So the original code I have should work, but cannot figure out what on earth is causing it not to work.

I know all the cells match, the names match, etc. just cannot figure it out.

 

[StephenCrump]

It looks like this distils down to ...

- The problem lies with some other code (not posted) which is supposed to copy data from "Case-Call Audit" to "Temp Data"
- That code isn't copying any data?

Can your post your complete code?

 

[Crystalyzer]

Can you post all the worksheets with the XL2BB tool? I'm afraid without that I won't be able to continue to assist troubleshooting.

 

[Daburger]

Hi @StephenCrump and @Crystalyzer,

The full code is mentioned in my first post above. Unfortunately, there is no other code.
I must also say, the other sheet where the above original code worked, was a much simpler sheet without each "section"...there was only 1 section.
This could explain why it isn't working...my apologies if this made it confusing.

My apologies again if what I am saying does not make much sense. I am still learning VBA code and is taking me a long time to get a grasp of it. I am very much an amateur here.
I have now installed XL2BB and have copied the code below as minisheets below:

Case-Call Audit:

Book2.xlsm
	A	B	C	D	E	F	G	H	I
1		Call Audit
2		Agent:	bob
3		Channel:	phone
4
5		Call ID:	1122334455
6		Call Date:	5-11-21 12:00 AM
7		Other data 1:
8		Other data 2:
9		Other data 3:
10		Other Data Date:	18-May-21
11		Audit Date:	06-Aug-21
12
13				Yes	No	N/A	KPI Category	KPI	Score
14		Section 1
15		Q1		1			Section 1	KPI 1	1
16		Q2		1			Section 1	KPI 2	1
17		Q3		1			Section 1	KPI 3	1
18		Q4			1		Section 1	KPI 4	0
19		Q5			1		Section 1	KPI 5	0
20		Q6			1		Section 1	KPI 6	0
21		Q7		1			Section 1	KPI 7	1
22		Total:		4	3	0	Section 1	Total:	57.14%
23		Score:				57.14%
24		Section 2
25		Q8		1			Section 2	KPI 8	1
26		Q9		1			Section 2	KPI 9	1
27		Q10		1			Section 2	KPI 10	1
28		Q11				1	Section 2	KPI 11	N/A
29		Q12			1		Section 2	KPI 12	0
30		Total:		3	1	1	Section 2	Total:	75.00%
31		Score:				75.00%
32		Section 3
33		Q13		1			Section 3	KPI 13	1
34		Q14		1			Section 3	KPI 14	1
35		Q15		1			Section 3	KPI 15	1
36		Q16		1			Section 3	KPI 16	1
37		Q17		1			Section 3	KPI 17	1
38		Q18		1			Section 3	KPI 18	1
39		Q19			1		Section 3	KPI 19	0
40		Q20		1			Section 3	KPI 20	1
41		Q21		1			Section 3	KPI 21	1
42		Q22		1			Section 3	KPI 22	1
43		Q23		1			Section 3	KPI 23	1
44		Q24		1			Section 3	KPI 24	1
45		Q25				1	Section 3	KPI 25	N/A
46		Q26		1			Section 3	KPI 26	1
47		Total:		12	1	1	Section 3	Total:	92.31%
48		Score:				92.31%
49		Section 4
50		Q27		1			Section 4	KPI 27	1
51		Q28		1			Section 4	KPI 28	1
52		Q29		1			Section 4	KPI 29	1
53		Q30			1		Section 4	KPI 30	0
54		Q31			1		Section 4	KPI 31	0
55		Q32			1		Section 4	KPI 32	0
56		Q33		1			Section 4	KPI 33	1
57		Q34		1			Section 4	KPI 34	1
58		Q35			1		Section 4	KPI 35	0
59		Q36			1		Section 4	KPI 36	0
60		Q37			1		Section 4	KPI 37	0
61		Q38				1	Section 4	KPI 38	N/A
62		Q39				1	Section 4	KPI 39	N/A
63		Q40		1			Section 4	KPI 40	1
64		Total:		6	6	2	Section 4	Total:	50.00%
65		Score:				50.00%
66		Section 5
67		Q41		1			Section 5	KPI 41	1
68		Q42		1			Section 5	KPI 42	1
69		Q43		1			Section 5	KPI 43	1
70		Q44		1			Section 5	KPI 44	1
71		Q45		1			Section 5	KPI 45	1
72		Total:		5	0	0	Section 5	Total:	100.00%
73		Score:				100.00%
74
75			Overall Total:	18	10	3
76			Overall Score:	64.29%
77		Comments:
78
79
80
Case-Call Audit

Cell Formulas
Range	Formula
D22:F22	D22	=SUM(D15:D21)
I15:I21,I67:I71,I50:I63,I33:I46,I25:I29	I15	=IF(D15=1,1,IF(E15=1,0,"N/A"))
I22,I72,I64,I47,I30	I22	=F23
F23,F73,F65,F48,F31	F23	=D22/(D22+E22)
D72:F72,D30:F30	D30	=SUM(D25:D29)
D64:F64,D47:F47	D47	=SUM(D33:D46)
D75:F75	E75	=SUM(E22,E30,E64,E72)
D76	D76	=D75/(D75+E75)

Cells with Data Validation
Cell	Allow	Criteria
A1:I80	Any value

Temp Data:
- Temp Data sheet is as the name suggests. It stores only the last audit that I previously added. nothing more. This was the way the sheet was given to me. If what I am doing can be done without this...that would be so good!!!

Book2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ
1	Agent:	Channel:	Call ID:	Call Date:	Other data 1:	Other data 2:	Other data 3:	Other Data Date:	Audit Date:	Issue:	KPI 1	KPI 2	KPI 3	KPI 4	KPI 5	KPI 6	Section 1 Total Score	KPI 7	KPI 8	KPI 9	KPI 10	KPI 11	Section 2 Total Score	KPI 12	KPI 13	KPI 14	KPI 15	KPI 16	KPI 17	KPI 18	KPI 19	KPI 20	KPI 21	KPI 22	KPI 23	KPI 24	KPI 25	Section 3 Total Score	KPI 26	KPI 27	KPI 28	KPI 29	KPI 30	KPI 31	KPI 32	KPI 33	KPI 34	KPI 35	KPI 36	KPI 37	KPI 38	KPI 39	Section 4 Total Score	KPI 40	KPI 41	KPI 42	KPI 43	KPI 44	Section 5 Total Score	Overall Total:	Overall Score:	Comments:
2	1	1	1	1	1
Temp Data

Case-Call RAW Data:
- Case-Call RAW Data has the same fields as Temp data, however, it stores each and every call audit that I add.

Book2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ
1	Agent:	Channel:	Call ID:	Call Date:	Other data 1:	Other data 2:	Other data 3:	Other Data Date:	Audit Date:	Issue:	KPI 1	KPI 2	KPI 3	KPI 4	KPI 5	KPI 6	Section 1 Total Score	KPI 7	KPI 8	KPI 9	KPI 10	KPI 11	Section 2 Total Score	KPI 12	KPI 13	KPI 14	KPI 15	KPI 16	KPI 17	KPI 18	KPI 19	KPI 20	KPI 21	KPI 22	KPI 23	KPI 24	KPI 25	Section 3 Total Score	KPI 26	KPI 27	KPI 28	KPI 29	KPI 30	KPI 31	KPI 32	KPI 33	KPI 34	KPI 35	KPI 36	KPI 37	KPI 38	KPI 39	Section 4 Total Score	KPI 40	KPI 41	KPI 42	KPI 43	KPI 44	Section 5 Total Score	Overall Total:	Overall Score:	Comments:
2
3
4
Case-Call RAW Data

I hope this helps!
Again, I really do appreciate all the help you are both providing, and anyone else who jumps in to help as well. Greatly appreciated.

 

[Crystalyzer]

As @StephenCrump suggested, the problem is this line because the worksheet Case-Call RAW Data contains NO data past the headers in Row 1. This line causes the code to attempt to select the row directly after the last row in the spreadsheet which cannot be done.

Range("A1").End(xlDown).Offset(1, 0).Select

I've simplified your code to this and it works fine.

Sub add_record()

    Sheets("Temp Data").Range("A2:BJ2").Copy
    Sheets("Case-Call RAW Data").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Case-Call Audit").Select

 

[Daburger]

Hi Crystalyzer,

You and @StephenCrump are right and your formula did the trick.
I miss understood beforehand but it has just clicked for me and now your explanations just clicked!!

Thanks again to you both for your time, your explanations, and especially your patience!

Cheers

 

[Crystalyzer]

Happy to help!