Vba to copy formulae

QuestionEdit

QUESTION:

I¡¯m taking your suggestion to create a macro to update the calculation of the VLOOKUP in an effort to improve speed of update/calculation. I need to run the vlookup twice a day after importing my data set.

 I used Record Macro to copy S4 to AG4 (this is my one line of formulas) from row 4 to row 828.  But I want to change this to copy to bottom of list by checking column A for last row of data.  I tried copy the formula you had given me in the past buy I couldn¡¯t get it working.  Then, I copy and paste special ¨C values from S5:AG5 to bottom of list.  I left S4:AG4 intentionally so that I can retain the vlookup formula, although if you could suggest a way for me to do this so that I don¡¯t keep this line of vlookup formula in S4:AG4 in case somebody else delete line 4.  This is a shared workbook so there might be a chance this line can get deleted.  In other words, how can I copy the actual vlookup formulae into the macro and still make it easily to make edits.

I think you can¡¯t use macros in a shared workbook so I would have to unshared the workbook to run this macro, which is a little bit of a pain since there are 5-6 people working on the file throughout the day.

Sub Update_Data() ' ' Update_Data Macro ' ' Keyboard Shortcut: Ctrl+w '

   ActiveSheet.ShowAllData
    
   Range("S4:AG4").Select
   Selection.Copy
   
   ActiveWindow.LargeScroll Down:=27
   Range("S4:AG828").Select
   ActiveSheet.Paste
   Range("T827:AG828").Select
   Range("AG828").Activate
   ActiveWindow.ScrollRow = 1534
   ActiveWindow.ScrollRow = 5
   Range("S5:AG5").Select
   ActiveWindow.LargeScroll Down:=8
   ActiveWindow.SmallScroll Down:=-18
   ActiveWindow.ScrollRow = 5
   
   Application.CutCopyMode = False
   Range("S5:AG5").Select
   ActiveWindow.LargeScroll Down:=27
   Range("S5:AG828").Select
   Selection.Copy
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Range("Z828").Select
   Application.CutCopyMode = False
   ActiveWindow.ScrollRow = 1534
   ActiveWindow.ScrollRow = 5
   Range("G1").Select

End Sub

ANSWER: Mike,

Just realized I didn't answer the part of you question about finding the last row in column A. I have revised the macro to include that capability. See below.

If you take out all the scrolling and selecting which are not necessary, your macro boils down to this:

Sub Update_Data() ' ' Update_Data Macro ' ' Keyboard Shortcut: Ctrl+w '

  ActiveSheet.ShowAllData
   
  Range("S4:AG4").Copy Range("S4:AG828")

  with Range("S5:AG828")
    .Copy
    .PasteSpecial Paste:=xlPasteValues
  End with

End Sub

I don't have your formula, but you could change your code to just enter the formula and it would be something like this

Sub Update_Data() ' ' Update_Data Macro ' ' Keyboard Shortcut: Ctrl+w '

  dim lastrow as Long  
  ActiveSheet.ShowAllData
   
  lastrow = cells(rows.count,"A").End(xlup).row
  with Range("S5:AG" & lastrow)
     ' put in your own formula below
    .Formula = "=Vlookup($R5,Sheet3!$A2:$O5000,column()-18,False)"
    .Copy
    .PasteSpecial Paste:=xlPasteValues
  End with

End Sub

then you would not have to worry about row 4. Of course if your formulas are different in each column, it would be bit more challenging, but could probably still be done. right now I envision the code an pulling in a subset of your data from another location and the type of formula I put in there should work for that scenario.

to the best of my knowledge, shared workbooks can run macros. You just can't edit the macros while the workbook is shared. I could be wrong, but that is my recollection.

---------- FOLLOW-UP ----------

QUESTION:

Also, why does it give me an error when I debug the program and try to run the line ActiveSheet.ShowAllData when the sheet is already showing all data. I want this as a check in case there are filters on.

Here's my feeble attempt to write the code.

' Keyboard Shortcut: Ctrl+w '

 Dim lastrow As Long
 
 ActiveSheet.ShowAllData
 lastrow = Cells(Rows.Count, "A").End(xlUp).Row
 
 Range("S4:AG4").Copy Range("S4:AG" & lastrow)

 With Range("S5:AG" & lastrow)
   .Copy
   .PasteSpecial Paste:=xlPasteValues
 End With

End Sub

AnswerEdit

after you copy the formula you can do

Application.Calculate

then you don't have to worry about the calculation setting.

Here is what I understand. You will keep formulas in rows 4 and 5 and you want to populate the row 5 formulas from row 5 down to the last row. Only rows S to AG will be used.

S4:AG4 will have nothing done to it. (won't be copied, won't be pasted)

ShowAllData raises an error unless there is an active filter. You can easily avoid that error.

Sub CopyStuff()

' Keyboard Shortcut: Ctrl+w '

Dim lastrow As Long, r as Range

On Error Resume Next

ActiveSheet.ShowAllData

On Error goto 0

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

set r = Range("S5:AG" & lastrow)

Range("S5:AG5").Copy r

With r
  .Copy
  .PasteSpecial Paste:=xlPasteValues
End With

End Sub

Hopefully that is what you want.