Run-Time 1004 error?

K

KristenB

New member
Joined
Jan 2, 2020
Messages
35
Reaction score
0
Points
0
Excel Version(s)
365
I am attempting to copy values in columns to a separate worksheet into three columns. As you can see, the values are all on the same worksheet (Travel Expense Codes) and are all being copied to the worksheet (Data Entry Summary). On the Travel Expense Codes worksheet, There are a total of twelve columns and I need to copy them in sets of 3 columns because of how the data is setup, to the 'Data Entry Summary' worksheet into only three columns. I hope that makes sense but I am going to use the 'Data Entry Summary' worksheet to create a summary of like items. This is currently a very manual process that turns many rows of data into approximately 5-6 rows of 3 pieces of data for data entry into our system.

Okay, enough summary, I am attempting to copy the first 3 columns to the columns on the separate worksheet. Then the second 3 columns to the columns on the separate worksheet starting at the first blank row, then the third 3 columns, and so on. However, when I run my macro, I am getting a Run-time 1004 Select method of range class failed. Does anyone know what this means and what I need to do with the following code to fix it? Any help is greatly appreciated! :)


Code: 
Sub DataEntrySummary()


'''DataEntrySummary creation Keyboard Shortcut: ctrl Shift + Q'''


Dim LastRow As Long


Worksheets("Data Entry Summary").Visible = True


Worksheets("Travel Expense Codes").Range("A2:C58").Select
Selection.Copy
Sheets("Data Entry Summary").Select
Range("A2").Select
ActiveSheet.Paste




Worksheets("Travel Expense Codes").Range("D2:F58").Select
Selection.Copy
Sheets("Data Entry Summary").Select
'Range("A2").Select
'ActiveSheet.Paste
Cells(Range("a1000000").End(xlUp).row + 1, 3).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False




Worksheets("Travel Expense Codes").Range("G2:I58").Select
Selection.Copy
Sheets("Data Entry Summary").Select
'Range("A2").Select
'ActiveSheet.Paste
Cells(Range("a1000000").End(xlUp).row + 1, 3).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False




Worksheets("Travel Expense Codes").Range("J2:L58").Select
Selection.Copy
Sheets("Data Entry Summary").Select
'Range("A2").Select
'ActiveSheet.Paste
Cells(Range("a1000000").End(xlUp).row + 1, 3).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False


End Sub
 
Last edited by a moderator:
I suspect you want:
Code: 
Sub DataEntrySummary()
'DataEntrySummary creation Keyboard Shortcut: ctrl Shift + Q'''
Worksheets("Data Entry Summary").Visible = True
Set rngSource = Worksheets("Travel Expense Codes").Range("A2:L58")
For colm = 1 To 10 Step 3
  Sheets("Data Entry Summary").Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(rngSource.Rows.Count, 3).Value = rngSource.Columns(colm).Resize(, 3).Value
Next colm
End Sub
 
Absolutely genius!! It worked like a charm!! Thank you so much! You are a rockstar in my book. :) I had no idea that the coding could be so simple!! I'm still learning this VBA coding thing so my coding is often quite 'beginner-ish'. Thank you for your help!!
 
You must log in or register to reply here.
Back
Top