Excel VBA – OnClick Button Functionality

microsoft excelvbaworksheet-function

I have this worksheet that contains columns "code, operation, title, date, name, description, status". The format are all in general.

code:4566, 4899, 4987, 4988, 4989  
operation:X,Y,Z,X,Y  
title:XX,YY,ZZ,RR,XXY  
date: (the date column is not blank)  
name:Adam,Edward,Adam,Kris,Chris  
description: (some rows has data for this column, some are blank)  
status: active, inactive, closed

I am trying to copy rows which their 'name' column has "Adam" or "Edward" AND 'status' column with "active" to new sheet that contain columns "code,title,date,name,description,status" only. (I dont want 'operation' column in new sheet)

at this point, I do not mind if some of my description cells in rows are empty. I still want it in the results.

by the end the return values would be only 2 rows : 4566 and 4987
I could do it manually. But i wanna try if it could be possibly done by vba with one click(automation process)

Thank you.

P/S : apologize for not including what I have for now, here it is

Options Explicit

Sub Button1_Click()

Dim myRow As Long 'for finding last row  
Dim xlast As Integer 'x is the last row  
Dim sht As Worksheet 'original sheet  
Dim newsht As Worksheet 'sheet with new data  

Set sht = ThisWorkbook.Worksheets("Sheet1")  
Set newsht = ThisWorkbook.Worksheets("Sheet2")

myCol = code  
xlast = sht.Cells(Rows.Count, myCol).End(xlUp).Row  
'go through my code column for non-blank

For x = 1 To xlast 'from 1st row till last row  
(tab)    If (sheet.name.Cells() = "Adam" OR "Edward") And (sheet.status.Cells() = "active")  
(tab)    Then newsht.Rows.Value(code,title,date,name,description,status) = sheet.Rows().Value(code,title,date,name,description,status)  
    End If  
Next

End Sub

Best Answer

There are a few problems with your example code:

sheet.name.Cells() = "Adam" OR "Edward"

  • you need to check one cell at a time, rather than Cells()
  • to refer to the value of the cell, you need to refer to the cell Value property, ie sheet.range().value = "Adam"
  • each comparison (Adam or Edward) needs to be evaluated separately, ie range().value = "Adam" OR range().value = "Edward"

newsht.Rows.Value(code,title,date,name,description,status)

  • Value doesn't take named ranges like this
  • this code doesn't use the iterator i to say which line to copy the data to in sheet2

Try the code below. You can assign it to a button if you wish. You'll need to change the range dat and newdat to suit your sheet. The main change from your code is that offset is used to iterate through the various rows and columns (offset from the upper left corner of the data block) to check each line of data. Each check or copy is coded explicitly.

Sub macro2()

Set sht = ThisWorkbook.Worksheets("Sheet1")
Set newsht = ThisWorkbook.Worksheets("Sheet2")
'Set dat = sht.Range("p9")
Set dat = sht.Range("code").Cells(1, 1)
Set newdat = newsht.Range("c2")

'initialise counters
i = 1
j = 1

'set headings on sheet 2
newdat.Offset(0, 0).Value = dat.Offset(0, 0).Value 'copy code
newdat.Offset(0, 1).Value = dat.Offset(0, 2).Value 'copy title
newdat.Offset(0, 2).Value = dat.Offset(0, 3).Value 'copy date
newdat.Offset(0, 3).Value = dat.Offset(0, 4).Value 'copy name
newdat.Offset(0, 4).Value = dat.Offset(0, 5).Value 'copy descr
newdat.Offset(0, 5).Value = dat.Offset(0, 6).Value 'copy status



Do While dat.Offset(i, 0).Value <> "" 'loop till code data goes blank
  If ((dat.Offset(i, 4).Value = "Adam" Or dat.Offset(i, 4).Value = "Edward") And dat.Offset(i, 6).Value = "active") Then 'check conditions
    newdat.Offset(j, 0).Value = dat.Offset(i, 0).Value 'copy code
    newdat.Offset(j, 1).Value = dat.Offset(i, 2).Value 'copy title
    newdat.Offset(j, 2).Value = dat.Offset(i, 3).Value 'copy date
    newdat.Offset(j, 3).Value = dat.Offset(i, 4).Value 'copy name
    newdat.Offset(j, 4).Value = dat.Offset(i, 5).Value 'copy descr
    newdat.Offset(j, 5).Value = dat.Offset(i, 6).Value 'copy status
    j = j + 1
  End If

  i = i + 1
Loop


End Sub
Related Question