Need Help Editing Another Person's old Macro

[crinaldi90]

I am stepping into an old macro somebody else wrote that is being used for an important file and need help fixing this small error that I know somebody out there will know how to fix fairly quickly. The macro is supposed to delete the data in row 4 of each tab and move all the rows up by one to incorporate the new month’s data. However, what’s actually happening is that the month headers (in column A) aren't moving up correctly, and the numbers are doing what they should. The macro is listed below and i am listed a piece of one of the tabs from the excel doc i am working with, as you can see the labels stay the same as the numbers move up. Let me know if what I am asking makes sense and wether you have any questions.



For i = 4 To Sheets.Count


Sheets(i).Activate
If ActiveSheet.Name = "Data-Billings" Then GoTo done
ActiveSheet.Range("B5:X16").Select
Selection.Copy
Range("B4").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("B1").Select

Next i

done:


End Sub


BEFORE MACRO

Profitablity as of February 2014</SPAN>
</SPAN>	DX</SPAN>
Month</SPAN>	Core Fee Revenue</SPAN>	Production Revenue</SPAN>	Ancillary Revenue</SPAN>	Fulfillment Costs</SPAN>	Freight Costs</SPAN>	Other Costs</SPAN>	Provision</SPAN>	Profit</SPAN>	Gross Sales $</SPAN>	Net Sales $</SPAN>	Gross Sales Units</SPAN>
Feb-13</SPAN>	18,586 </SPAN>	4,517 </SPAN>	4,559 </SPAN>	3,832 </SPAN>	2,140 </SPAN>	132 </SPAN>	1,337 </SPAN>	22,894 </SPAN>	165,329 </SPAN>	102,521 </SPAN>	17,081 </SPAN>
Mar-13</SPAN>	28,454 </SPAN>	1,784 </SPAN>	12,200 </SPAN>	4,353 </SPAN>	2,879 </SPAN>	262 </SPAN>	(1,880)</SPAN>	33,066 </SPAN>	190,931 </SPAN>	148,066 </SPAN>	18,685 </SPAN>
Apr-13</SPAN>	27,817 </SPAN>	3,852 </SPAN>	3,785 </SPAN>	3,002 </SPAN>	2,923 </SPAN>	372 </SPAN>	(840)</SPAN>	28,316 </SPAN>	189,208 </SPAN>	148,987 </SPAN>	18,850 </SPAN>
May-13</SPAN>	38,913 </SPAN>	6,009 </SPAN>	2,644 </SPAN>	4,581 </SPAN>	3,847 </SPAN>	200 </SPAN>	(1,937)</SPAN>	37,001 </SPAN>	235,069 </SPAN>	199,712 </SPAN>	24,731 </SPAN>
Jun-13</SPAN>	44,535 </SPAN>	5,226 </SPAN>	2,190 </SPAN>	5,413 </SPAN>	2,989 </SPAN>	471 </SPAN>	(5,669)</SPAN>	37,410 </SPAN>	259,685 </SPAN>	225,853 </SPAN>	27,878 </SPAN>
Jul-13</SPAN>	23,518 </SPAN>	2,701 </SPAN>	5,800 </SPAN>	3,614 </SPAN>	2,205 </SPAN>	(19)</SPAN>	(2,001)</SPAN>	24,219 </SPAN>	176,880 </SPAN>	134,312 </SPAN>	20,830 </SPAN>
Aug-13</SPAN>	36,967 </SPAN>	4,969 </SPAN>	4,073 </SPAN>	5,655 </SPAN>	3,916 </SPAN>	231 </SPAN>	(3,587)</SPAN>	32,620 </SPAN>	235,226 </SPAN>	196,688 </SPAN>	30,475 </SPAN>
Sep-13</SPAN>	17,955 </SPAN>	1,511 </SPAN>	2,953 </SPAN>	2,915 </SPAN>	1,787 </SPAN>	285 </SPAN>	1,041 </SPAN>	18,474 </SPAN>	133,703 </SPAN>	100,459 </SPAN>	15,201 </SPAN>
Oct-13</SPAN>	42,704 </SPAN>	3,756 </SPAN>	4,838 </SPAN>	3,704 </SPAN>	3,776 </SPAN>	195 </SPAN>	(3,016)</SPAN>	40,608 </SPAN>	261,801 </SPAN>	215,260 </SPAN>	25,073 </SPAN>
Nov-13</SPAN>	36,037 </SPAN>	5,321 </SPAN>	2,004 </SPAN>	4,881 </SPAN>	2,788 </SPAN>	434 </SPAN>	625 </SPAN>	35,885 </SPAN>	219,035 </SPAN>	187,239 </SPAN>	21,782 </SPAN>
Dec-13</SPAN>	31,906 </SPAN>	5,312 </SPAN>	3,561 </SPAN>	4,750 </SPAN>	3,011 </SPAN>	519 </SPAN>	(1,266)</SPAN>	31,233 </SPAN>	200,550 </SPAN>	165,410 </SPAN>	20,756 </SPAN>
Jan-14</SPAN>	36,021 </SPAN>	1,697 </SPAN>	2,117 </SPAN>	4,473 </SPAN>	2,948 </SPAN>	336 </SPAN>	3,238 </SPAN>	35,316 </SPAN>	217,509 </SPAN>	187,987 </SPAN>	24,208 </SPAN>
Feb-14</SPAN>	11,057 </SPAN>	4,899 </SPAN>	3,450 </SPAN>	3,777 </SPAN>	1,390 </SPAN>	107 </SPAN>	1,951 </SPAN>	16,083 </SPAN>	119,736 </SPAN>	69,555 </SPAN>	14,460 </SPAN>
TTY</SPAN>	375,884 </SPAN>	47,037 </SPAN>	49,615 </SPAN>	51,118 </SPAN>	34,459 </SPAN>	3,393 </SPAN>	(13,341)</SPAN>	370,231 </SPAN>	2,439,333 </SPAN>	1,979,528 </SPAN>	262,929 </SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=2><COL><COL><COL><COL span=2><COL><COL><COL></COLGROUP>

AFTER MACRO

Profitablity as of February 2014</SPAN>
</SPAN>	DX</SPAN>
Month</SPAN>	Core Fee Revenue</SPAN>	Production Revenue</SPAN>	Ancillary Revenue</SPAN>	Fulfillment Costs</SPAN>	Freight Costs</SPAN>	Other Costs</SPAN>	Provision</SPAN>	Profit</SPAN>	Gross Sales $</SPAN>	Net Sales $</SPAN>	Gross Sales Units</SPAN>
Feb-13</SPAN>	28,454 </SPAN>	1,784 </SPAN>	12,200 </SPAN>	4,353 </SPAN>	2,879 </SPAN>	262 </SPAN>	(1,880)</SPAN>	33,066 </SPAN>	190,931 </SPAN>	148,066 </SPAN>	18,685 </SPAN>
Mar-13</SPAN>	27,817 </SPAN>	3,852 </SPAN>	3,785 </SPAN>	3,002 </SPAN>	2,923 </SPAN>	372 </SPAN>	(840)</SPAN>	28,316 </SPAN>	189,208 </SPAN>	148,987 </SPAN>	18,850 </SPAN>
Apr-13</SPAN>	38,913 </SPAN>	6,009 </SPAN>	2,644 </SPAN>	4,581 </SPAN>	3,847 </SPAN>	200 </SPAN>	(1,937)</SPAN>	37,001 </SPAN>	235,069 </SPAN>	199,712 </SPAN>	24,731 </SPAN>
May-13</SPAN>	44,535 </SPAN>	5,226 </SPAN>	2,190 </SPAN>	5,413 </SPAN>	2,989 </SPAN>	471 </SPAN>	(5,669)</SPAN>	37,410 </SPAN>	259,685 </SPAN>	225,853 </SPAN>	27,878 </SPAN>
Jun-13</SPAN>	23,518 </SPAN>	2,701 </SPAN>	5,800 </SPAN>	3,614 </SPAN>	2,205 </SPAN>	(19)</SPAN>	(2,001)</SPAN>	24,219 </SPAN>	176,880 </SPAN>	134,312 </SPAN>	20,830 </SPAN>
Jul-13</SPAN>	36,967 </SPAN>	4,969 </SPAN>	4,073 </SPAN>	5,655 </SPAN>	3,916 </SPAN>	231 </SPAN>	(3,587)</SPAN>	32,620 </SPAN>	235,226 </SPAN>	196,688 </SPAN>	30,475 </SPAN>
Aug-13</SPAN>	17,955 </SPAN>	1,511 </SPAN>	2,953 </SPAN>	2,915 </SPAN>	1,787 </SPAN>	285 </SPAN>	1,041 </SPAN>	18,474 </SPAN>	133,703 </SPAN>	100,459 </SPAN>	15,201 </SPAN>
Sep-13</SPAN>	42,704 </SPAN>	3,756 </SPAN>	4,838 </SPAN>	3,704 </SPAN>	3,776 </SPAN>	195 </SPAN>	(3,016)</SPAN>	40,608 </SPAN>	261,801 </SPAN>	215,260 </SPAN>	25,073 </SPAN>
Oct-13</SPAN>	36,037 </SPAN>	5,321 </SPAN>	2,004 </SPAN>	4,881 </SPAN>	2,788 </SPAN>	434 </SPAN>	625 </SPAN>	35,885 </SPAN>	219,035 </SPAN>	187,239 </SPAN>	21,782 </SPAN>
Nov-13</SPAN>	31,906 </SPAN>	5,312 </SPAN>	3,561 </SPAN>	4,750 </SPAN>	3,011 </SPAN>	519 </SPAN>	(1,266)</SPAN>	31,233 </SPAN>	200,550 </SPAN>	165,410 </SPAN>	20,756 </SPAN>
Dec-13</SPAN>	36,021 </SPAN>	1,697 </SPAN>	2,117 </SPAN>	4,473 </SPAN>	2,948 </SPAN>	336 </SPAN>	3,238 </SPAN>	35,316 </SPAN>	217,509 </SPAN>	187,987 </SPAN>	24,208 </SPAN>
Jan-14</SPAN>	11,057 </SPAN>	4,899 </SPAN>	3,450 </SPAN>	3,777 </SPAN>	1,390 </SPAN>	107 </SPAN>	1,951 </SPAN>	16,083 </SPAN>	119,736 </SPAN>	69,555 </SPAN>	14,460 </SPAN>
Feb-14</SPAN>	11,057 </SPAN>	4,899 </SPAN>	3,450 </SPAN>	3,777 </SPAN>	1,390 </SPAN>	107 </SPAN>	1,951 </SPAN>	16,083 </SPAN>	119,736 </SPAN>	69,555 </SPAN>	14,460 </SPAN>
TTY</SPAN>	358,487 </SPAN>	50,152 </SPAN>	40,865 </SPAN>	50,542 </SPAN>	32,970 </SPAN>	3,238 </SPAN>	(9,510)</SPAN>	353,248 </SPAN>	2,368,138 </SPAN>	1,901,017 </SPAN>	258,704 </SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=2><COL><COL><COL><COL span=2><COL><COL><COL></COLGROUP>

 

[skywriter]

It's not deleting anything, it's copying the data in the cells B5:X16 in to cell B4.

Do you simply want to delete row4 in the top example, just as if you were to select the row and right click and select delete from the menu or is there something else you want to do?

 

[skywriter]

If you only want to delete the fourth row, you can try this code.

Make sure to try it on a copy first.

Sub DeleteRowFour()

Dim i As Long


For i = 4 To Sheets.Count


With Sheets(i)
    
    If .Name = "Data-Billings" Then GoTo done
    .Range("4:4").Delete Shift:=xlUp
    
End With


Next i

done:


End Sub

 

[crinaldi90]

Yep, thanks for your help and clarity