0

I am developing a tool that takes rows of data from one sheet (named "Data"), and then paste it into another sheet (named "Printable Report"). Each row coincides with a different job, and will get its own report. When I run the code I get the run-time error 438. I have tried different solutions and none seem to be working. If someone could take look at this and help me figure out where my problem is, that would be greatly appreciated. The error is occurring on the 8th line down. Thanks!

Sub ExportToReport()

Worksheets("Data").Activate


Dim x As Long, y As Long, z As Long


y = Sheets("Data").Range("A1", Range("A1").End(xlDown)).Rows.Count

For x = 2 To y

    Sheets("Data").Cells(x, 4).Select
    Selection.Copy
    Worksheets("Printable Report").Activate
    Sheets("Printable Report").Range("B1").Select
    Selection.Paste

    Worksheets("Data").Activate
    Sheets("Data").Cells(x, 9).Select
    Selection.Copy
    Worksheets("Printable Report").Activate
    Sheets("Printable Report").Range("B2").Select
    Selection.Paste

    Worksheets("Data").Activate
    Sheets("Data").Cells(x, 8).Select
    Selection.Copy
    Worksheets("Printable Report").Activate
    Sheets("Printable Report").Range("B3").Select
    Selection.Paste

    Worksheets("Data").Activate
    Sheets("Data").Cells(x, 10).Select
    Selection.Copy
    Worksheets("Printable Report").Activate
    Sheets("Printable Report").Range("B4").Select
    Selection.Paste

    Worksheets("Data").Activate
    Sheets("Data").Cells(x, 11).Select
    Selection.Copy
    Worksheets("Printable Report").Activate
    Sheets("Printable Report").Range("B5").Select
    Selection.Paste

    Worksheets("Data").Activate
    Sheets("Data").Cells(x, 12).Select
    Selection.Copy
    Worksheets("Printable Report").Activate
    Sheets("Printable Report").Range("B6").Select
    Selection.Paste

    Worksheets("Data").Activate
    Sheets("Data").Cells(x, 29).Select
    Selection.Copy
    Worksheets("Printable Report").Activate
    Sheets("Printable Report").Range("B7").Select
    Selection.Paste

    Worksheets("Data").Activate
    Sheets("Data").Cells(x, 25).Select
    Selection.Copy
    Worksheets("Printable Report").Activate
    Sheets("Printable Report").Range("B8").Select
    Selection.Paste

    Worksheets("Data").Activate
    Sheets("Data").Cells(x, 15).Select
    Selection.Copy
    Worksheets("Printable Report").Activate
    Sheets("Printable Report").Range("B9").Select
    Selection.Paste
    'Then insert a set number of cells on print report when move to next row on Data
    Sheets("Printable Report").Range("A1").EntireRow.Offset(1).Resize(36).Insert Shift:=xlDown
Next x
End Sub
JJung
  • 9
  • 1

2 Answers2

2

You don't specify where you get your error, but change the structure of all the blocks of code like this:

Sheets("Data").Cells(x, 4).Select
Selection.Copy
Worksheets("Printable Report").Activate
Sheets("Printable Report").Range("B1").Select
Selection.Paste

To this: 

Sheets("Printable Report").Range("B" & x - 1).Value = Sheets("Data").Cells(x,4).Value

and it should work and be easier to read / refactor later.

Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • 2
    I was thinking that formatting may be lost in a direct value transfer. –  Jul 03 '18 at 18:11
  • @Jeeped - you are right. I was too lazy this time to really consider that - which tells me I need to stop for the day :)\ – Scott Holtzman Jul 03 '18 at 18:22
2

Change this,

Worksheets("Data").Activate
Sheets("Data").Cells(x, 9).Select
Selection.Copy
Worksheets("Printable Report").Activate
Sheets("Printable Report").Range("B2").Select
Selection.Paste

... to this,

Worksheets("Data").Cells(x, 9).Copy _
  destination:=Worksheets("Printable Report").Range("B2")

You should be able to repeat that change in methodology in a few more places further down in your code.

Please read How to avoid using Select in Excel VBA.