0

I have a running monthly report for accounting for our sales. Each day, I get our sales for each branch in one report. I have a Vlookup formula saved in cell E1 with a formula that will pull over the information from the report to another sheet depending on what I am working on. I can copy E1 and paste into each cell in a row with range of columns B-S and then i copy that row again and paste values so that it doesn't change my amounts. The next day, i repeat process on the next row so that it gives me a running monthly total per branch and then for the company as a whole. My monthly chart always is around 23 rows.(rows 6-28 in excel)

I need a macro that will do the following: on the 1st, i run the macro and it will copy my cell E1 and paste it into B6 - S6 row, then copy that row and paste values that row. Then on the 2nd of the month, i run the macro and it repeats but on row B7-S7. Then on the 3rd of the month, i run the macro and it repeats but on row B8-S8.

Is this possible?

This is what i have with just recording a macro.

Sub Charges()
'
'Charges Macro
'Run Formula for charges
'
' Keyboard Shortcut: Ctrl+Shift+C
'
    Range("E1").Select
    Selection.Copy
    Range("B8:S8").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
 End Sub
Rachel Mears
  • 27
  • 6
  • [Possible duplicate of Error in finding last used cell in VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba). Also, it's best practice to [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – BruceWayne Mar 05 '19 at 20:28
  • You can use the `VLOOKUP` function in VBA that way you dont need to store the formula in cell E1. Also, I know this code was taken from the Macro Recorder, but you want to avoid `.Select` and `.Activate`. – Zack E Mar 05 '19 at 20:28
  • so i am ok with basic excel but this is way above me so everything i am doing / learning is just by googling it. :) i work for a company that is use to doing everything by hand so currently we type each value into each cell in excel from a large report that prints each morning. I finally got the report in an excel format today from IT and i am trying to automate the move of the information from one excel report to another workbook for our sales. – Rachel Mears Mar 05 '19 at 21:00

1 Answers1

0

Perhaps this? You can transfer the values directly, obviating the need to copy and paste.

Read this on how to make code more efficient by avoiding Select/Activate.

Sub Charges()

Dim n As Long

n = WorksheetFunction.Max(6, Range("B" & Rows.Count).End(xlUp).Row + 1)

Range("B" & n & ":S" & n).Value = Range("E1").Value

End Sub

If you want to copy the formula and then overwrite with the value you can use this

Sub Charges()

Dim n As Long

n = WorksheetFunction.Max(6, Range("B" & Rows.Count).End(xlUp).Row + 1)

Range("E1").Copy Range("B" & n & ":S" & n)

Range("B" & n & ":S" & n).Value = Range("B" & n & ":S" & n).Value

End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26
  • This worked for get that cell value down and across the row like i want! however, it just does the value and not the Vlookup formula. :( Zack commented that you can code the vlookup formula into the VBA instead of in the cell block itself. Any idea how? This is my formula =VLOOKUP(E$3,'NOTEBOOK WORKSHEET'!$C$7:$D$41,2,FALSE) – Rachel Mears Mar 05 '19 at 21:16
  • SJR, ok, so i copied that same vba to 2 other sheets in the same workbook but these 2 sheets have a totals line across the bottom of all cells B-S so it keeps running the macro and putting it down under the totals line. Anything i can do to stop that? – Rachel Mears Mar 06 '19 at 13:00
  • Yes. If the totals are on a fixed row hard-code that rather than working from the bottom of the sheet up (rows.count). Otherwise you could use Find to return the total row. – SJR Mar 06 '19 at 15:13