run macro on next row without changing macro.

[Saoirse]

Have a macro with two formulas, a count if and a countifs pulling information from another sheet. But each time I try to run in on sheet B it changes the formulas within the macro. I am very very new to macros and am a beginner trying to create a tracker for my boss and could use any advice I can get to make this awesome.

 

[Saurabhj]

Hi, Welcome to Mr. Excel board.

Please share the macro you created and sheet information to understand the query better.

Thanks,
Saurabh

 

[Saoirse]

'
' Macro1 Macro
'

'

Range("B3").Select
ActiveCell.FormulaR1C1 = "=COUNTIF('main page'!RC[5]:R[9996]C[5],""E923928"")"
Range("C3").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('main page'!R[-1]C[4]:R[12]C[4],""E923928"",'main page'!R[-1]C[3]:'main page'!R[12]C[3],""DB_A1"")"
Range("C4").Select
End Sub


above is the macro and sheet "a" is like this only about 10,000 rows:

item num	case num	date sent	adj type	case type	reason code	analyst	adj amount
15318149​	15318149​	2022-01-18​	AD	DS	DB_A1	E923928	$42.36​
15321643​	15321643​	2022-01-18​	AD	DS	DB_R	E987654	$35.72​
15300400​	15300400​	2022-01-18​	AD	DS	DB_K2	E455146	$35.73​
15340823​	15340823​	2022-01-18​	AD	DS	DB_B2	E754898	$35.74​
15422512​	15422512​	2022-01-18​	AD	DS	DB_B4	E987456	$35.72​

and sheet "b" is:

e923928
date	cases worked	prep cases
1/1/2022​	4​	4​
1/2/2022​
1/3/2022​

 

[Saurabhj]

Hi, I run the macro on the sheet you shared and I didn't find any change in the code. The code always works on B3 and C3 cell as per macro.

Can you share what exactly you are getting and what is the expected result ?

Note: In R1C1 style when numbers are within bracket, e.g. R[1]C[1], it works as relative reference.
When used without bracket, e.g. R1C1, it works as absolute reference.

Option Explicit
Sub myMacro()
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF('main page'!RC[5]:R[9996]C[5],""E923928"")"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = _
    "=COUNTIFS('main page'!R[-1]C[4]:R[12]C[4],""E923928"",'main page'!R[-1]C[3]:'main page'!R[12]C[3],""DB_A1"")"
    Range("C4").Select
End Sub

Book1
	A	B	C	D	E	F	G	H	I	J
1	item num	case num	date sent	adj type	case type	reason code	analyst	adj amount
2	15318149	15318149	2022-01-18	AD	DS	DB_A1	E923928	$42.36
3	15321643	15321643	2022-01-18	AD	DS	DB_R	E987654	$35.72
4	15300400	15300400	2022-01-18	AD	DS	DB_K2	E455146	$35.73
5	15340823	15340823	2022-01-18	AD	DS	DB_B2	E754898	$35.74
6	15422512	15422512	2022-01-18	AD	DS	DB_B4	E987456	$35.72
7
8
9
10
main page

Book1
	A	B	C	D
1	e923928
2	date	cases worked	prep cases
3	1/1/2022	0	1
4	1/2/2022
5	1/3/2022
6
7
8
b

Cell Formulas
Range	Formula
B3	B3	=COUNTIF('main page'!G3:G9999,"E923928")
C3	C3	=COUNTIFS('main page'!G2:G15,"E923928",'main page'!F2:'main page'!F15,"DB_A1")

 

[Saoirse]

My goal is to be able to paste my report (from my other program) into the "pasted report" sheet every day. Each day I would paste over the old one with the new statistics. Then my goal is to run a macro to have the formulas update for the next line down for each tab based on the formulas. I am having the formulas change when I try to run the macro on the next line down. It changes the A1 cell and has it go to A2 then A3. My mini-sheets are not pasting onto this form, I followed all your instructions, but it just pasted the previous thing I had on the clipboard. This is on sheet 2 this is from sheet 1

 

[Saurabhj]

Hi,

Sorry didn't understand the requirement.

Great if you can use XL2BB to copy the sheet data. Would help to understand the issue and resolve the query.

Thanks,
Saurabh

 

[Saoirse]

I'm so sorry, let me try again, So I have a program from my company that I copy statistics from and paste it onto the "mainpage" sheet. (sheet 1) I would like to run a macro that pulls info from sheet 1 and inputs it onto "E923928" (sheet 2) on the next empty line (which would be for the next date). I cannot seem to get the formulas to run on the next line down. The formula will either change or it will only fill out on the first line. Can you help me correct my formulas to have the macro run on the next row?

This table is on Sheet 1 "mainpage"

book2.xlsm
	A	B	C	D	E	F	G	H
1	item num	case num	date sent	adj type	case type	reason code	analyst	adj amount
2	15318149	15318149	2022-01-18	AD	DS	DB_A1	E923928	$42.36
3	15321643	15321643	2022-01-18	AD	DS	DB_R	E987654	$35.72
4	15300400	15300400	2022-01-18	AD	DS	DB_K2	E455146	$35.73
5	15340823	15340823	2022-01-18	AD	DS	DB_B2	E754898	$35.74
6	15422512	15422512	2022-01-18	AD	DS	DB_B4	E987456	$35.72
7	15442871	15442871	2022-01-18	AD	DS	DB_X	E345672	$35.72
8	15483563	15483563	2022-01-18	AD	DS	DB_A1	E923928	$35.72
9	15483625	15483563	2022-01-18	AD	DS	DB_B2	E923928	$35.72
10	15513751	15513751	2022-01-18	AD	DS	DB_B2	E987654	$35.72
11	15518168	15518168	2022-01-18	AD	DS	DB_A1	E923928	$35.72
12	15532274	15532274	2022-01-18	AD	DS	DB_A1	E345672	$35.72
13	15532278	15532278	2022-01-18	AD	DS	DB_A1	E923928	$35.72
14	15540036	15540036	2022-01-18	AD	DS	DB_X	E987654	$35.72
15	15543594	15543594	2022-01-18	AD	DS	DB_X	E987456	$35.72
main page

This table in on sheet 2

book2.xlsm
	A	B	C	D
1	e923928
2	date	The formula for this column counts how many times the code name "E923928" shows up in column G on the table from the sheet "mainpage"	This formula is supposed to count column F when it says "DB_A1" but only when column G has "E923928"	This column should take the number in column C and multiply it by 7.
3	1/1/2022	4	4	28
4	1/2/2022	The macro won't run on this line. I input a new table onto the "mainpage" sheet everyday and I want to run the macro daily with the new information.
E923928

Cell Formulas
Range	Formula
B3	B3	=COUNTIF('main page'!G3:G9999,"E923928")
C3	C3	=COUNTIFS('main page'!G2:G15,"E923928",'main page'!F2:'main page'!F15,"DB_A1")
D3	D3	=SUM(C3*7)

 

[Saurabhj]

Hi,

Thanks for sharing the details.

Please check below code:

Sub copyData()

Dim nextRow As Integer

nextRow = Sheets("E923928").Cells(Rows.Count, 1).End(xlUp).Row + 1

Sheets("E923928").Range("A" & nextRow) = Format(Now, "MM-DD-YYYY")
Sheets("E923928").Range("B" & nextRow) = WorksheetFunction.CountIf(Sheets("mainpage").Range("G:G"), "E923928")
Sheets("E923928").Range("C" & nextRow) = WorksheetFunction.CountIfs(Sheets("mainpage").Range("G:G"), "E923928", Sheets("mainpage").Range("F:F"), "DB_A1")
Sheets("E923928").Range("D" & nextRow) = Sheets("E923928").Range("C" & nextRow) * 7
End Sub

 

[Saurabhj]

Hi, If you like to change formula then check below:

Modified formula is in B4, C4 and D4 cells.

Book2
	A	B	C	D
1	e923928
2	date	The formula for this column counts how many times the code name "E923928" shows up in column G on the table from the sheet "mainpage"	This formula is supposed to count column F when it says "DB_A1" but only when column G has "E923928"	This column should take the number in column C and multiply it by 7.
3	01-01-2022	4	4	28
4	31-01-2022	5	4	28
E923928

Cell Formulas
Range	Formula
B3	B3	=COUNTIF(mainpage!G3:G9999,"E923928")
C3	C3	=COUNTIFS(mainpage!G2:G15,"E923928",mainpage!F2:mainpage!F15,"DB_A1")
D3	D3	=SUM(C3*7)
B4	B4	=COUNTIF(mainpage!$G:$G,"E923928")
C4	C4	=COUNTIFS(mainpage!$G:$G,"E923928",mainpage!$F:$F,"DB_A1")
D4	D4	=C4*7

 

[Saoirse]

YES!! That totally works!! Thank you so much!

I have one last request. Is there a way to make the date input the previous day?
The one now is for today's date.

Sheets("E923928").Range("A" & nextRow) = Format(Now, "MM-DD-YYYY")