1

I have a "form" on one worksheet and 9 cells in it that are used to enter data in range F1-F9.

Then I want to copy that data from those cells to one line on the 2nd page. I have a recorded macro that does this, but it takes about 45 seconds to process. It selects each sheet manually followed by a copy/paste operation into the new line. There has got to be a more optimal way to do this.

Here is part of the code:

Sheets("INBOUND").Select
Range("b3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Sheets("TAG").Select
Range("f7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("INBOUND").Select
Range("c3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Sheets("TAG").Select
Range("f9").Select
Application.CutCopyMode = False
Community
  • 1
  • 1
Tony Arnold
  • 649
  • 2
  • 7
  • 15
  • 2
    Do not use `.Select` and in your case, using Copy / Paste is also unecessary. Simply use `Sheets("targetsheetname").Range("B3").Value = Sheets("sourcesheetname").Range("A1").Value`, obviously adapting sheet names and cell ranges. For further research: [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – nicolaus-hee Apr 27 '15 at 14:00
  • Furthering what @nhee said, it's best to move away from using .select and find other ways of getting values. You can still copy/paste without using .select, but I've found the quickest way is to, as mentioned, set the two range values equal to eachother. – BruceWayne Apr 27 '15 at 14:56
  • @nhee & user3578951 - Thanks for the suggestions. Its really just that simple? Thats kind of a surprise, but cool. – Tony Arnold Apr 28 '15 at 11:49

1 Answers1

1

Try this Subroutine:

Sub ValuesCopy()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim sourceRanges As Variant
    Dim destinationRanges As Variant
    Dim i As Long

    Set sourceSheet = Sheets("TAG") 'Set to source sheet
    Set destinationSheet = Sheets("INBOUND") 'set to destination sheet

    sourceRanges = Array("F5", "F7", "F9") 'Enter ranges here. Must have the same number as destination.
    destinationRanges = Array("A3", "B3", "C3") 'Enter ranges here.  Must have the same number as source.

    'loops through arrays of ranges and copying values
    For i = 0 To UBound(sourceRanges)
        destinationSheet.Range(destinationRanges(i)) = sourceSheet.Range(sourceRanges(i))
    Next i
End Sub

This is a good, generic method to do what you want.

Mr. Mascaro
  • 2,693
  • 1
  • 11
  • 18
  • Does using the variables provide a performance increase rather than using say, Sheets("TAG") everytime? I assume it does? – Tony Arnold Apr 29 '15 at 15:24
  • It will provide a small performance increase, but in such a short program the perceived improvement will be negligible. I just showed some better coding practices. – Mr. Mascaro Apr 29 '15 at 17:13