How to get rid of Copy&Paste using Cell function In Excel VBA with double loop?

Question

I am optimizing a very old macros code and I noticed that it does useless action to copy data from Sheet "Source" to "Test" and afterwards - to the right destination sheet "Overview".

Is there any way to get rid of an extra sheet "Test" and Selection, CutCopyMode, et cetra?

Dim i As Integer
   Dim m, n As Integer
   Rmin = Application.InputBox("Min row.")
   Rmax = Application.InputBox("Max row.")

   For i = Rmin To Rmax
    For j = 1 To 99
      Sheets("Source").Select
      Cells(i, j).Select
      Application.CutCopyMode = False
      Selection.Copy

      Sheets("Test").Select
      Cells(1, j).Select
      ActiveSheet.Paste

    Next j
      Sheets("Test").Select
      Range("A1:PK1").Select
       Selection.Copy

      Sheets("Overview").Select
      Range("A2").Select
      ActiveSheet.Paste

     Range("A3:I54").Select
     Selection.Copy
    Application.CutCopyMode = False
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\...." & File & ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False

    Next i
        Sheets("Source").Select
        Cells(Rmax, 1).Select

Thank you!

Add. information: Macros is used to create a PDF of each user selected row in the Sheet "Source", afterwards data form selection at Sheet "Overview" is used to create PDF. And I am new to the whole VBA environment, however, I try my best. I am already using the Application.ScreenUpdating to reduce run time.

For working code in need of optimizations, [Code review](https://codereview.stackexchange.com/) is a better place. For select replacement, look here: [How to avoid using Select in Excel](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Vincent G, Jul 11 '16 at 08:45

score 0 · Answer 1 · answered Jul 11 '16 at 08:55

0

Its not entirely clear to me which cell gets copied where in the long run, but in general you can always avoid the .copy / .paste commands by using the syntax below:

  For i = Rmin To Rmax
    For j = 1 To 99
      Sheets("Overview").Cells(2,j).Value = Sheets("Source").Cells(i, j).Value

    Next j

In this case, you specify the value of the cell on the left to be equal to the content of the cell on the right.

Best regards seulberg1

answered Jul 11 '16 at 08:55

seulberg1

955
1
7
19

Thanks a lot! I tried something like this but unsuccessfully. Now everything is fine. – The Economist Jul 12 '16 at 05:49

score 0 · Answer 2 · answered Jul 11 '16 at 08:57

From what I can see you can easily skip the pasting to the test-sheet. Try this:

Dim i As Integer
Dim m, n As Integer
Rmin = Application.InputBox("Min row.")
Rmax = Application.InputBox("Max row.")

For i = Rmin To Rmax
  Sheets("Source").Select    'Move to the source sheet
  Range("A" & i & ":PK" & i).Select  'Select the row to copy
  Application.CutCopyMode = False
  Selection.Copy     'Copy the row

  Sheets("Overview").Select  'Move to "Destination"-sheet
  Range("A2").Select   'Paste source into A2 (for some reason)
  ActiveSheet.Paste

  Range("A3:I54").Select  'Select the are you want to create a pdf of
  'And create the PDF...
  Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\...." & File & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

Next i  'Move to next row in source sheet

Sheets("Source").Select   'Move back to source
Cells(Rmax, 1).Select     'Select the last cell.

Hope this helps speed things up :)

Thanks a lot! It cut the run time time by 80%. :) – The Economist Jul 12 '16 at 05:48 — The Economist, Jul 12 '16 at 05:48

How to get rid of Copy&Paste using Cell function In Excel VBA with double loop?

2 Answers2