0

I'm trying to copy cells (strings) from one sheet ('Summary Document' all on row 4) to a table in the second sheet ('Worker 1', starting at B10). I believe I have defined all elements correctly. When I run the first time, it works. however, it gives me a 1004 error on the second run. I'm pretty sure it is my if/then statement that looks for the next empty line, but not sure what the issue is.

Dim FileName As String, FileNumber As String, RecordingPeriod As String, RecordingType As String, Auditor As String, ReviewDate As String`
Dim A1 As String, A2 As String, A3 As String, A4 As String, A5 As String, A6 As String
Worksheets("Summary Document").Select
FileName = Range("C4")
FileNumber = Range("D4")
RecordingPeriod = Range("E4")
RecordingType = Range("F4")
Auditor = Range("G4")
ReviewDate = Range("H4")
A1 = Range("J4")
A2 = Range("K4")
A3 = Range("L4")
A4 = Range("M4")
A5 = Range("N4")
A6 = Range("O4")
Worksheets("Worker 1").Select
Worksheets("Worker 1").Range("B9").Select
If Worksheets("Worker 1").Range("B9").Offset(1, 0) <> "" Then
Worksheets("Worker 1").Range("B9").End(x1Down).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = FileName
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = FileNumber
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = RecordingPeriod
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = RecordingType
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Auditor
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ReviewDate
ActiveCell.Offset(0, 2).Select
ActiveCell.Value = A1
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = A2
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = A3
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = A4
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = A5
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = A6
End Sub
Community
  • 1
  • 1
Senstrek
  • 3
  • 1

1 Answers1

0

Also in Addition to the comment above, the code can be simplfied greatly:

Sub hhhhh()
Dim ows as Worksheet
Dim tws As Worksheet
Dim rw as long

Set ows = Worksheets("Summary Document")
Set tws = Worksheets("Worker 1")

If tws.Range("B10") <> "" Then
    rw = 10
Else
    rw = tws.Range("B9").End(xlDown).Row + 1
End If

tws.Range("B" & rw & ":G" & rw).Value = ows.Range("C4:H4").Value
tws.Range("I" & rw & ":N" & rw).Value = ows.Range("J4:O4").Value


End Sub

One should always avoid using the .Select when possible. See This Link for great options.

Community
  • 1
  • 1
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • thank you for the quick response - works perfectly now. The simpler code and link is also very much appreciated!! – Senstrek Jan 13 '16 at 19:28
  • @Senstrek if this was the correct answer please consider marking as correct by clicking the green check mark by the answer. – Scott Craner Jan 17 '16 at 02:42