VBA command button [Solved]

Srojassg 6 Posts Friday September 21, 2018Registration date October 2, 2018 Last seen - Sep 21, 2018 at 04:37 PM - Latest reply: vcoolio 1200 Posts Thursday July 24, 2014Registration dateModeratorStatus October 12, 2018 Last seen
- Oct 2, 2018 at 10:59 PM
Hi Everyone,

I need help creating the following:

I want to create a CommandButton that when clicked it will check column "CB" for "YES" and will input on a different worksheet called "Update Report" what is in columns "A, B, D, AO, BF, BG"

I would really appreciate the help!

Thanks,

Sergio
See more 

Your reply

10 replies

Best answer
ac3mark 10678 Posts Monday June 3, 2013Registration dateModeratorStatus October 19, 2018 Last seen - Updated by ac3mark on 21/09/18 at 04:46 PM
1
Thank you
OK, I will help, but you have to do the work!

Here is how to get the value out of a sheet:

Thevariable = ThisWorkbook.Worksheets("SheetName").Range("M3").value

That will populate the "Thevariable" Variable with the value in Sheetname.M3.

Then, to cut the line:

ActiveSheet.Range("A" & whatrow).EntireRow.Select
Selection.Copy


Then to past the entire line, it is:


Private Function copyRow(whatrow, whatsheet)
ActiveSheet.Paste Destination:=Worksheets(whatsheet).Range("A" & whatrow)
Application.CutCopyMode = False
End Function


To deploy the above code:

copyrow(3,"SheetNameOfDestination")


In order to go through each line, you use:

X = Cells(ThisWorkbook.Worksheets(whatsheet).Rows.Count, 1).End(xlDown).Row


Now you loop using X on column CB!

Have fun!

Thank you, ac3mark 1

Something to say? Add comment

CCM has helped 1257 users this month

Srojassg 6 Posts Friday September 21, 2018Registration date October 2, 2018 Last seen - Sep 24, 2018 at 08:49 AM
Thank you for the help! I really appreciate it!
Respond to ac3mark
Best answer
vcoolio 1200 Posts Thursday July 24, 2014Registration dateModeratorStatus October 12, 2018 Last seen - Sep 22, 2018 at 03:56 AM
1
Thank you
Hello Sergio,

Following is the link to a sample file that I used to help another Poster over a year ago:-

http://ge.tt/347f3nr2

That Poster's query was very similar to yours and may be just what could help you out. Click on the "RUN" button to see it work.

The code in that file is as follows:-

Sub Test()

Application.ScreenUpdating = False

Sheet2.UsedRange.Offset(1).Clear

With Sheet1.[A1].CurrentRegion
        .AutoFilter 16, "Yes"
        Union(.Columns("B:D"), .Columns("H:J"), .Columns("M")).Offset(1).Copy
        Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
        .AutoFilter
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


It transfers only certain columns of data per row from Sheet1 to Sheet2. If you read through the code carefully, you should be able to adapt it to your situation.

Let us know if you need any further help.

I hope that this helps.

Cheerio,
vcoolio.

Thank you, vcoolio 1

Something to say? Add comment

CCM has helped 1257 users this month

Srojassg 6 Posts Friday September 21, 2018Registration date October 2, 2018 Last seen - Sep 24, 2018 at 08:49 AM
Thank you very much for your help. That code worked perfectly and very easy to implement and use!
ac3mark 10678 Posts Monday June 3, 2013Registration dateModeratorStatus October 19, 2018 Last seen - Sep 24, 2018 at 04:36 PM
I hope you learned something!
vcoolio 1200 Posts Thursday July 24, 2014Registration dateModeratorStatus October 12, 2018 Last seen - Sep 25, 2018 at 03:15 AM
You're welcome Sergio.
I'm glad that we were able to help.

Cheerio,
vcoolio.
Srojassg 6 Posts Friday September 21, 2018Registration date October 2, 2018 Last seen - Oct 1, 2018 at 10:11 AM
You guys were amazing last time. It helped me tremendously! And I wanted to ask you one more question.

If I wanted to ask you if it was possible to save it into a different Workbook. I have been trying to change the code but I am having a hard time.

Thank you in advance.
Respond to vcoolio
Best answer
vcoolio 1200 Posts Thursday July 24, 2014Registration dateModeratorStatus October 12, 2018 Last seen - Oct 2, 2018 at 07:26 AM
1
Thank you
Hello Sergio,

I've amended the above code for you which will allow you to transfer the data to a new work book, as follows:-


Sub Test2()

        Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
        Dim wb As Workbook

Application.ScreenUpdating = False

Workbooks.Open Filename:="C:\Users\YOUR FILE PATH HERE\MONSTER.xlsx" '---->Change the file name to suit.
Set wb = Workbooks("MONSTER.xlsx") '---->Change the file name to suit.
wb.Activate

With ws.[A1].CurrentRegion
        .AutoFilter 16, "Yes"
        Union(.Columns("B:D"), .Columns("H:J"), .Columns("M")).Offset(1).Copy
        wb.Worksheets("Sheet1").Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
        .AutoFilter
End With
    
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


The code assumes that you already have created a new workbook.
You'll have to change the file path and file name to suit yourself.

I hope that this helps.

Cheerio,
vcoolio.

...........and yes, I do have a file named "MONSTER.xlsx" !!

Thank you, vcoolio 1

Something to say? Add comment

CCM has helped 1257 users this month

Srojassg 6 Posts Friday September 21, 2018Registration date October 2, 2018 Last seen - Oct 2, 2018 at 09:47 AM
Thank you very much. It helps a lot! Your code is always easy to follow and implement!

.......and hey, I used to create a used to name my important files funny names so no one would suspect to open it.
vcoolio 1200 Posts Thursday July 24, 2014Registration dateModeratorStatus October 12, 2018 Last seen - Oct 2, 2018 at 10:59 PM
You're welcome Sergio. Glad that I was able to help again.

Cheerio,
vcoolio.
Respond to vcoolio