how do i make this code work on a seperate sheet

S

sheppy72

Board Regular
Joined
Jun 7, 2006
Messages
104
Hello again.
Well its completely confused me.
This code provided kindly by Rick Rothstein works a treat if it is in the sheet where the data is, however i was trying to modifiy it so that it worked from a seperate sheet via a command button. The coded Rick provided is

Sub DeleteZeroSums()
Dim LastRow As Long, LastCol As Long
Range("D146:DS146").Value = Range("D145:DS145").Value
Range("DU17:DU144").Value = Range("DT17:DT144").Value
Range("D146:DS146").Replace 0, "=0", xlWhole
Range("DU17:DU144").Replace 0, "=0", xlWhole
On Error Resume Next
Range("DU17:DU144").SpecialCells(xlCellTypeFormulas).EntireRow.Delete
LastRow = Cells(Rows.Count, "DT").End(xlUp).Row + 1
Range("D" & LastRow & ":DS" & LastRow).SpecialCells(xlCellTypeFormulas).EntireColumn.Delete
LastCol = Cells(LastRow, Columns.Count).End(xlToLeft).Column
Rows(LastRow).Clear
Columns(LastCol + 2).Clear
End Sub


I have set up 2 command buttons on sheet 1 and this is the code i am trying with out success. Any ideas please why it will not work like this?
Sub CommandButton1_Click()
Application.ScreenUpdating = False


Workbooks.Open Filename:=ThisWorkbook.Path & "\base data.xls"
Sheets("base data 1").Range("a1:dt170").Copy



Windows("file control.xls").Activate

Sheets.Add.Name = ("base data sheet1")

Sheets("base data sheet1").Range("a1").PasteSpecial Paste:=xlPasteAll



End Sub

Private Sub CommandButton2_Click()
Dim LastRow As Long, LastCol As Long
'Sheets("base data sheet1").Select

Sheets("base data sheet1").Range("D146:DS146").Value = Range("D145:DS145").Value
Sheets("base data sheet1").Range("DU17:DU144").Value = Range("DT17:DT144").Value
Sheets("base data sheet1").Range("D146:DS146").Replace 0, "=0", xlWhole
Sheets("base data sheet1g").Range("DU17:DU144").Replace 0, "=0", xlWhole
On Error Resume Next
Sheets("base data sheet1").Range("DU17:DU144").SpecialCells(xlCellTypeFormulas).EntireRow.Delete
LastRow = Cells(Rows.Count, "DT").End(xlUp).Row + 1
Sheets("base data sheet1").Range("D" & LastRow & ":DS" & LastRow).SpecialCells(xlCellTypeFormulas).EntireColumn.Delete
LastCol = Cells(LastRow, Columns.Count).End(xlToLeft).Column
Rows(LastRow).Clear
Columns(LastCol + 2).Clear


Sheets("base data sheet1").Range("D16:DS16").FormulaR1C1 = "=AVERAGE(R[-2]C:R[-1]C)"
Sheets("base data sheet1").Range("c17:c144").FormulaR1C1 = "=AVERAGE(RC[-2]:RC[-1])"


End Sub
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
The code simply hasn't chosen a sheet on which to perform these actions. The code will then run for the active sheet by default.

Suppose that the sheet for which the code needs to run is called "MyData". Then add the line at the beginning of the code:
Code: 
Sheets("MyData").select

The code then becomes:
Code: 
Sub DeleteZeroSums()
Sheets("MyData").Select
Dim LastRow As Long, LastCol As Long
Range("D146:DS146").Value = Range("D145:DS145").Value
Range("DU17:DU144").Value = Range("DT17:DT144").Value
Range("D146:DS146").Replace 0, "=0", xlWhole
Range("DU17:DU144").Replace 0, "=0", xlWhole
On Error Resume Next
Range("DU17:DU144").SpecialCells(xlCellTypeFormulas).EntireRow.Delete
LastRow = Cells(Rows.Count, "DT").End(xlUp).Row + 1
Range("D" & LastRow & ":DS" & LastRow).SpecialCells(xlCellTypeFormulas).EntireColumn.Delete
LastCol = Cells(LastRow, Columns.Count).End(xlToLeft).Column
Rows(LastRow).Clear
Columns(LastCol + 2).Clear
End Sub
 
Upvote 0
I already have that line in my modified code and when the command button is pressed it still does not perfrom the removal of the sum 0 rows and coloumns. The sheet remains exactly as it was copied and pasted in from the origianl file.
Any other ideas?
 
Upvote 0
Since it is not operating on the sheet you want, check which sheet is the active sheet by putting before the Dim LastRow:

Code: 
Debug.Print (ActiveSheet.Name)

Also, run your code up until the part that is giving you trouble and then cycle through your code by pressing F8 to see a step by step breakdown of what your code is doing. This should giver you a better clue as to what is going on. If you are unsure how to do whis then have a look at:

http://www.cpearson.com/excel/debug.htm
 
Upvote 0
Here are the sheets for anyone to have a look at to help me
<a href=http://www.filedropper.com/basedata><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >share files free</a></div>

<a href=http://www.filedropper.com/filecontrol><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >online backup storage</a></div>

The code for removing all the rows and columns with 0 as the sum just does not seem to work when I use the command button.
If I put the code directly in the sheet with the data to sort and rn the macro it works every time. Very confused as to what I am doing wrong here.

Anyone any ideas how to get this to work or a different way to delete the rows and coloumns with 0 as the sum when I click on the command button on another sheet?
 
Upvote 0
The problem you are having is because you are trying to treat the base_data workbook as if it were a worksheet inside of the file_control workbook. You need to point all the ranges to the Sheet1 in the base_data workbook. Change your CommandButton2's Click event code to this and I think the code will begin working for you. Note that I also fixed your range references for the two AVERAGE formula assignment lines you added (you were setting them to span the length of the original rows and columns before all the zero rows/columns were removed instead of to their new dimensions).

Code: 
Sub CommandButton2_Click()
  Dim LastRow As Long, LastCol As Long, WB As Workbook, WS As Worksheet
  Set WS = Workbooks("base_data.xls").Worksheets("Sheet1")
  WS.Range("D146:DS146").Value = WS.Range("D145:DS145").Value
  WS.Range("DU17:DU144").Value = WS.Range("DT17:DT144").Value
  WS.Range("D146:DS146").Replace 0, "=0", xlWhole
  WS.Range("DU17:DU144").Replace 0, "=0", xlWhole
  On Error Resume Next
  WS.Range("DU17:DU144").SpecialCells(xlCellTypeFormulas).EntireRow.Delete
  LastRow = WS.Cells(Rows.Count, "DT").End(xlUp).Row + 1
  WS.Range("D" & LastRow & ":DS" & LastRow).SpecialCells(xlCellTypeFormulas).EntireColumn.Delete
  LastCol = WS.Cells(LastRow, Columns.Count).End(xlToLeft).Column
  WS.Rows(LastRow).Clear
  WS.Columns(LastCol + 2).Clear
  WS.Range("D16:" & Cells(16, LastCol - 1).Address(0, 0)).FormulaR1C1 = "=AVERAGE(R[-2]C:R[-1]C)"
  WS.Range("c17:c" & (LastRow - 2)).FormulaR1C1 = "=AVERAGE(RC[-2]:RC[-1])"
End Sub
 
Upvote 0
Rick it worked a treat with one small mod.

I change your line to this
Set WS = Workbooks("file control.xls").Worksheets("base data Sheet1").

Thank you so much. I have spent all day trying to get this to work.
 
Upvote 0
sheppy72 said:
Rick it worked a treat with one small mod.

I change your line to this
Set WS = Workbooks("file control.xls").Worksheets("base data Sheet1").

Thank you so much. I have spent all day trying to get this to work.
Click to expand...
You are quite welcome, but I must confess I am confused at the change you made... the code I posted worked perfectly for the files you uploaded and I would think the change you made would not work at all. I'm guessing your actual setup is different from that which you uploaded. In any event, I'm glad you got it working.
 
Upvote 0
Ok, last question of the day as I fear I will hard program my laptop with a hammer soon!!!

using the code as created by Rick which works a dream I now need to only copy the cells from C16 to the last column -1 and the last row -1 (so as to not copy the sum lines) and then paste this in a new sheet in cell A1.

I need it to select the last column and row as this may vary when i use the code on other sheets with more rows and columns on it.

I can create the new sheet etc but just can not figure out how to select the range to copy.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
How restrict copy data from form to sheet based on five days within current month
Replies
3
Views
110
Ali M
A
M
  • Solved
vba to sum up each column for each employee on column (A)
Replies
6
Views
330
kvsrinivasamurthy
K
J
  • Question Question
VBA Search box and button code
Replies
0
Views
271
jlcodes
J
H
  • Question Question
Copy Data where Source Workbook contains Name that is in destination sheet
Replies
4
Views
153
howard
H
R
  • Question Question
Compose Email VBA Help
Replies
1
Views
121
Logit
Logit

Forum statistics

Threads
1,224,590
Messages
6,179,762
Members
452,940
Latest member
rootytrip

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top