Need to make macro smaller

[nealtd]

I made a macro that is too large to even post on here and will not run in excel because it is too large. Can anyone help me to make this smaller? I'm completely clueless. Took me almost a month to create this just to find out that it is too long. I should have been smarter.

Worksheets("sheet1").Select
SubmissionID = Range("a4")
BatchName = Range("b4")
AccountNumber = Range("c4")
DateRequested = Range("d4")
DateReviewed = Range("f4")
RetrievalAssociate = Range("g4")
DoesVolumeMatch = Range("j4")
WasCorrectMediaAttached = Range("k4")
WasPDFNamedCorrectly = Range("l4")
AppRedacted = Range("m4")
AdditionalAccount = Range("n4")
ResultsColumn = Range("o4")
SystemRecord = Range("p4")
FolderName = Range("q4")
SheetStructure = Range("r4")
Other = Range("s4")
SubmissionID2 = Range("a5")
BatchName2 = Range("b5")
AccountNumber2 = Range("c5")
DateRequested2 = Range("d5")
DateReviewed2 = Range("f5")
RetrievalAssociate2 = Range("g5")
DoesVolumeMatch2 = Range("j5")
WasCorrectMediaAttached2 = Range("k5")
WasPDFNamedCorrectly2 = Range("l5")
AppRedacted2 = Range("m5")
AdditionalAccount2 = Range("n5")
ResultsColumn2 = Range("o5")
SystemRecord2 = Range("p5")
FolderName2 = Range("q5")
SheetStructure2 = Range("r5")
Other2 = Range("s5")
SubmissionID3 = Range("a6")
BatchName3 = Range("b6")
AccountNumber3 = Range("c6")
DateRequested3 = Range("d6")
DateReviewed3 = Range("f6")
RetrievalAssociate3 = Range("g6")
DoesVolumeMatch3 = Range("j6")
WasCorrectMediaAttached3 = Range("k6")
WasPDFNamedCorrectly3 = Range("l6")
AppRedacted3 = Range("m6")
AdditionalAccount3 = Range("n6")
ResultsColumn3 = Range("o6")
SystemRecord3 = Range("p6")
FolderName3 = Range("q6")
SheetStructure3 = Range("r6")
Other3 = Range("s6")
SubmissionID4 = Range("a7")
BatchName4 = Range("b7")
AccountNumber4 = Range("c7")
DateRequested4 = Range("d7")
DateReviewed4 = Range("f7")
RetrievalAssociate4 = Range("g7")
DoesVolumeMatch4 = Range("j7")
WasCorrectMediaAttached4 = Range("k7")
WasPDFNamedCorrectly4 = Range("l7")
AppRedacted4 = Range("m7")
AdditionalAccount4 = Range("n7")
ResultsColumn4 = Range("o7")
SystemRecord4 = Range("p7")
FolderName4 = Range("q7")
SheetStructure4 = Range("r7")
Other4 = Range("s7")
SubmissionID5 = Range("a8")
BatchName5 = Range("b8")
AccountNumber5 = Range("c8")
DateRequested5 = Range("d8")
DateReviewed5 = Range("f8")
RetrievalAssociate5 = Range("g8")
DoesVolumeMatch5 = Range("j8")
WasCorrectMediaAttached5 = Range("k8")
WasPDFNamedCorrectly5 = Range("l8")
AppRedacted5 = Range("m8")
AdditionalAccount5 = Range("n8")
ResultsColumn5 = Range("o8")
SystemRecord5 = Range("p8")
FolderName5 = Range("q8")
SheetStructure5 = Range("r8")
Other5 = Range("s8")
SubmissionID6 = Range("a9")
BatchName6 = Range("b9")
AccountNumber6 = Range("c9")
DateRequested6 = Range("d9")
DateReviewed6 = Range("f9")
RetrievalAssociate6 = Range("g9")
DoesVolumeMatch6 = Range("j9")
WasCorrectMediaAttached6 = Range("k9")
WasPDFNamedCorrectly6 = Range("l9")
AppRedacted6 = Range("m9")
AdditionalAccount6 = Range("n9")
ResultsColumn6 = Range("o9")
SystemRecord6 = Range("p9")
FolderName6 = Range("q9")
SheetStructure6 = Range("r9")
Other6 = Range("s9")
SubmissionID7 = Range("a10")
BatchName7 = Range("b10")
AccountNumber7 = Range("c10")
DateRequested7 = Range("d10")
DateReviewed7 = Range("f10")
RetrievalAssociate7 = Range("g10")
DoesVolumeMatch7 = Range("j10")
WasCorrectMediaAttached7 = Range("k10")
WasPDFNamedCorrectly7 = Range("l10")
AppRedacted7 = Range("m10")
AdditionalAccount7 = Range("n10")
ResultsColumn7 = Range("o10")
SystemRecord7 = Range("p10")
FolderName7 = Range("q10")
SheetStructure7 = Range("r10")
Other7 = Range("s10")
SubmissionID8 = Range("a11")
BatchName8 = Range("b11")
AccountNumber8 = Range("c11")
DateRequested8 = Range("d11")
DateReviewed8 = Range("f11")
RetrievalAssociate8 = Range("g11")
DoesVolumeMatch8 = Range("j11")
WasCorrectMediaAttached8 = Range("k11")
WasPDFNamedCorrectly8 = Range("l11")
AppRedacted8 = Range("m11")
AdditionalAccount8 = Range("n11")
ResultsColumn8 = Range("o11")
SystemRecord8 = Range("p11")
FolderName8 = Range("q11")
SheetStructure8 = Range("r11")
Other8 = Range("s11")
SubmissionID9 = Range("a12")
BatchName9 = Range("b12")
AccountNumber9 = Range("c12")
DateRequested9 = Range("d12")
DateReviewed9 = Range("f12")
RetrievalAssociate9 = Range("g12")
DoesVolumeMatch9 = Range("j12")
WasCorrectMediaAttached9 = Range("k12")
WasPDFNamedCorrectly9 = Range("l12")
AppRedacted9 = Range("m12")
AdditionalAccount9 = Range("n12")
ResultsColumn9 = Range("o12")
SystemRecord9 = Range("p12")
FolderName9 = Range("q12")
SheetStructure9 = Range("r12")
Other9 = Range("s12")
SubmissionID10 = Range("a13")
BatchName10 = Range("b13")
AccountNumber10 = Range("c13")
DateRequested10 = Range("d13")
DateReviewed10 = Range("f13")
RetrievalAssociate10 = Range("g13")
DoesVolumeMatch10 = Range("j13")
WasCorrectMediaAttached10 = Range("k13")
WasPDFNamedCorrectly10 = Range("l13")
AppRedacted10 = Range("m13")
AdditionalAccount10 = Range("n13")
ResultsColumn10 = Range("o13")
SystemRecord10 = Range("p13")
FolderName10 = Range("q13")
SheetStructure10 = Range("r13")
Other10 = Range("s13")

Both of these go all the way to 250. I only put the first 10 of the top one and 2 of the bottom one.

If DoesVolumeMatch3 = "No" Then
.Offset(RowCount + 17, 1) = "Does volume match spreadsheet total?"
.Offset(RowCount + 17, 2) = AccountNumber3
.Offset(RowCount + 17, 3) = DateRequested3
.Offset(RowCount + 17, 4) = "No"
.Offset(RowCount + 17, 5) = "Yes"
End If
If WasCorrectMediaAttached3 = "No" Then
.Offset(RowCount + 18, 1) = "Was the correct media attached?"
.Offset(RowCount + 18, 2) = AccountNumber3
.Offset(RowCount + 18, 3) = DateRequested3
.Offset(RowCount + 18, 4) = "No"
.Offset(RowCount + 18, 5) = "Yes"
End If
If WasPDFNamedCorrectly3 = "No" Then
.Offset(RowCount + 19, 1) = "Was PDF named correctly?"
.Offset(RowCount + 19, 2) = AccountNumber3
.Offset(RowCount + 19, 3) = DateRequested3
.Offset(RowCount + 19, 4) = "No"
.Offset(RowCount + 19, 5) = "Yes"
End If
If AppRedacted3 = "No" Then
.Offset(RowCount + 20, 1) = "Was application redacted correctly?"
.Offset(RowCount + 20, 2) = AccountNumber3
.Offset(RowCount + 20, 3) = DateRequested3
.Offset(RowCount + 20, 4) = "No"
.Offset(RowCount + 20, 5) = "Yes"
End If
If AdditionalAccount3 = "No" Then
.Offset(RowCount + 21, 1) = "Were additional media/account numbers requested?"
.Offset(RowCount + 21, 2) = AccountNumber3
.Offset(RowCount + 21, 3) = DateRequested3
.Offset(RowCount + 21, 4) = "No"
.Offset(RowCount + 21, 5) = "Yes"
End If
If ResultsColumn3 = "No" Then
.Offset(RowCount + 22, 1) = "Was results column on spreadsheet correct?"
.Offset(RowCount + 22, 2) = AccountNumber3
.Offset(RowCount + 22, 3) = DateRequested3
.Offset(RowCount + 22, 4) = "No"
.Offset(RowCount + 22, 5) = "Yes"
End If
If SystemRecord3 = "No" Then
.Offset(RowCount + 23, 1) = "Was the System of Record documented correctly?"
.Offset(RowCount + 23, 2) = AccountNumber3
.Offset(RowCount + 23, 3) = DateRequested3
.Offset(RowCount + 23, 4) = "No"
.Offset(RowCount + 23, 5) = "Yes"
End If
If FolderName3 = "No" Then
.Offset(RowCount + 24, 1) = "Did the folder name match the spreadsheet?"
.Offset(RowCount + 24, 2) = AccountNumber3
.Offset(RowCount + 24, 3) = DateRequested3
.Offset(RowCount + 24, 4) = "No"
.Offset(RowCount + 24, 5) = "Yes"
End If
If SheetStructure3 = "No" Then
.Offset(RowCount + 25, 1) = "Is spreadsheet structure correct?"
.Offset(RowCount + 25, 2) = AccountNumber3
.Offset(RowCount + 25, 3) = DateRequested3
.Offset(RowCount + 25, 4) = "No"
.Offset(RowCount + 25, 5) = "Yes"
End If
If Other3 = "No" Then
.Offset(RowCount + 26, 1) = "Other (Please provide comment)"
.Offset(RowCount + 26, 2) = AccountNumber3
.Offset(RowCount + 26, 3) = DateRequested3
.Offset(RowCount + 26, 4) = "No"
.Offset(RowCount + 26, 5) = "Yes"
End If
If DoesVolumeMatch4 = "No" Then
.Offset(RowCount + 27, 1) = "Does volume match spreadsheet total?"
.Offset(RowCount + 27, 2) = AccountNumber4
.Offset(RowCount + 27, 3) = DateRequested4
.Offset(RowCount + 27, 4) = "No"
.Offset(RowCount + 27, 5) = "Yes"
End If
If WasCorrectMediaAttached4 = "No" Then
.Offset(RowCount + 28, 1) = "Was the correct media attached?"
.Offset(RowCount + 28, 2) = AccountNumber4
.Offset(RowCount + 28, 3) = DateRequested4
.Offset(RowCount + 28, 4) = "No"
.Offset(RowCount + 28, 5) = "Yes"
End If
If WasPDFNamedCorrectly4 = "No" Then
.Offset(RowCount + 29, 1) = "Was PDF named correctly?"
.Offset(RowCount + 29, 2) = AccountNumber4
.Offset(RowCount + 29, 3) = DateRequested4
.Offset(RowCount + 29, 4) = "No"
.Offset(RowCount + 29, 5) = "Yes"
End If
If AppRedacted4 = "No" Then
.Offset(RowCount + 30, 1) = "Was application redacted correctly?"
.Offset(RowCount + 30, 2) = AccountNumber4
.Offset(RowCount + 30, 3) = DateRequested4
.Offset(RowCount + 30, 4) = "No"
.Offset(RowCount + 30, 5) = "Yes"
End If
If AdditionalAccount4 = "No" Then
.Offset(RowCount + 31, 1) = "Were additional media/account numbers requested?"
.Offset(RowCount + 31, 2) = AccountNumber4
.Offset(RowCount + 31, 3) = DateRequested4
.Offset(RowCount + 31, 4) = "No"
.Offset(RowCount + 31, 5) = "Yes"
End If
If ResultsColumn4 = "No" Then
.Offset(RowCount + 32, 1) = "Was results column on spreadsheet correct?"
.Offset(RowCount + 32, 2) = AccountNumber4
.Offset(RowCount + 32, 3) = DateRequested4
.Offset(RowCount + 32, 4) = "No"
.Offset(RowCount + 32, 5) = "Yes"
End If
If SystemRecord4 = "No" Then
.Offset(RowCount + 33, 1) = "Was the System of Record documented correctly?"
.Offset(RowCount + 33, 2) = AccountNumber4
.Offset(RowCount + 33, 3) = DateRequested4
.Offset(RowCount + 33, 4) = "No"
.Offset(RowCount + 33, 5) = "Yes"
End If
If FolderName4 = "No" Then
.Offset(RowCount + 34, 1) = "Did the folder name match the spreadsheet?"
.Offset(RowCount + 34, 2) = AccountNumber4
.Offset(RowCount + 34, 3) = DateRequested4
.Offset(RowCount + 34, 4) = "No"
.Offset(RowCount + 34, 5) = "Yes"
End If
If SheetStructure4 = "No" Then
.Offset(RowCount + 35, 1) = "Is spreadsheet structure correct?"
.Offset(RowCount + 35, 2) = AccountNumber4
.Offset(RowCount + 35, 3) = DateRequested4
.Offset(RowCount + 35, 4) = "No"
.Offset(RowCount + 35, 5) = "Yes"
End If
If Other4 = "No" Then
.Offset(RowCount + 36, 1) = "Other (Please provide comment)"
.Offset(RowCount + 36, 2) = AccountNumber4
.Offset(RowCount + 36, 3) = DateRequested4
.Offset(RowCount + 36, 4) = "No"
.Offset(RowCount + 36, 5) = "Yes"
End If

If you have any ideas it would be greatly appreciated. Thank you in advance.

 

[nealtd]

Yes this isn't filling out the rows. Not quite what I want. Your sheet is accurate of what my sheet looks like. But none of the data is copying over to the next sheet(ACCOUNT_DETAILS).

 

[Snakehips]

Starting with an empty Account_Details sheet here is a sample of how that code is extracting from that data set for me.
I have removed some blank rows for display purposes here.

Excel Workbook
	B	C	D	E	F
5		42374
6		Bill
7		Batch 1
8		SB0001
9	Does volume match spreadsheet total?	12345	42370	No	Yes
10	Was the correct media attached?	12345	42370	No	Yes
11	Was PDF named correctly?	12345	42370	No	Yes
12	Was application redacted correctly?	12345	42370	No	Yes
13	Were additional media/account numbers requested?	12345	42370	No	Yes
14	Was results column on spreadsheet correct?	12345	42370	No	Yes
15	Was the System of Record documented correctly?	12345	42370	No	Yes
16	Did the folder name match the spreadsheet?	12345	42370	No	Yes
17	Is spreadsheet structure correct?	12345	42370	No	Yes
18	Other (Please provide comment)	12345	42370	No	Yes
19
20
21
22		42375
23		Sue
24		Batch 2
25		SB0002
26
27
28
29		42376
30		Fred
31		Batch 3
32		SB0003
33	Was the correct media attached?	12347	42372	No	Yes
34	Was PDF named correctly?	12347	42372	No	Yes
35
36	Were additional media/account numbers requested?	12347	42372	No	Yes
37	Was results column on spreadsheet correct?	12347	42372	No	Yes
38	Was the System of Record documented correctly?	12347	42372	No	Yes
39	Did the folder name match the spreadsheet?	12347	42372	No	Yes
40	Is spreadsheet structure correct?	12347	42372	No	Yes
41	Other (Please provide comment)	12347	42372	No	Yes
42
43
44
45		42377
46		Charlie
47		Batch 4
48		SB0004
Account_Details

????

 

[nealtd]

That looks like exactly what I need. Maybe I'm doing something wrong.

 

[Snakehips]

That looks like exactly what I need. Maybe I'm doing something wrong.

Good news / bad news then

Remember that that test code is dealing with two Sheets in the same workbook. Is that what you have?

Here is temporary like to my file......

https://www.dropbox.com/s/43rm2pb3hq9d9kk/Nealtd.xlsm?dl=0

 

[nealtd]

Snakehips,

You are the man! I appreciate everything. I have a question though. The spreadsheet that I'm copying over into has headers in the 12th row of the columns and a some have a header in the 2nd row and 12th row. And I believe that is throwing the formula off to where it is pasting in the middle or close to the bottom of the spreadsheet. Do you have any advice? Thanks again for everything.

 

[Snakehips]

You are welcome to the assistance, glad it is helping.

So far I have had to try and interpret your original code in order to hopefully determine your desired outcome.

If you can be more specific I can help with final tweaks.

If there are always headers in 12 then no problem.
Do you need all the blank rows that are generated by the Yes responses ?
Can you use my test file to show the format you want for the pasted data?