0

I'm having problems getting this to work. I just want to copy the second sheet from the left before the first sheet. Then copy info from what just became the second sheet to the new left most sheet. I get an error on SELECTION.Copy stating there is an expected variable missing.

Sub GenerateInvoice()
     Sheets(2).Select
     Sheets(2).Copy Before:=Sheets(1)
     Sheets(2).Select
     Range("H2:N2").Select
     SELECTION.Copy
     Sheets(1).Select
     Range("E11").Select
     SELECTION.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
End Sub
HalfWit
  • 31
  • 6

1 Answers1

1

Do you have any hidden sheets or non-worksheet sheets (chart sheets for example)?

You generally don't need select/activate:

Sub GenerateInvoice()
     Sheets(2).Copy Before:=Sheets(1)
     With Sheets(2).Range("H2:N2")
         Sheets(1).Range("E11").Resize(1, .Columns.Count).Value = .Value
     End With
End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • I have a hidden sheet that fills the allows me to put in a quantity and it generates the $$ for the qty listed. However I don't need the formulas copied, I only need the data. Basically I'm generating an invoice for every progress payment breakdown. – HalfWit Nov 22 '17 at 18:59
  • That code worked perfectly. I will have to look at it closer and see how it works. It's much more efficient than what I have been using. Thanks! – HalfWit Nov 22 '17 at 19:09
  • Them main thing is when you're only copy-pasting values it's much faster to just assign the values directly. Also not selecting things is a big improvement: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1 – Tim Williams Nov 22 '17 at 19:27