VBA command button
Solved/Closed
Srojassg
Posts
6
Registration date
Friday September 21, 2018
Status
Member
Last seen
October 2, 2018
-
Updated on Oct 7, 2018 at 12:24 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Oct 2, 2018 at 10:59 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Oct 2, 2018 at 10:59 PM
Related:
- VBA command button
- Cs 1.6 money command - Guide
- At button - Guide
- Vba case like - Guide
- Change computer name command line - Guide
- Net send command - Guide
3 responses
OK, I will help, but you have to do the work!
Here is how to get the value out of a sheet:
That will populate the "Thevariable" Variable with the value in Sheetname.M3.
Then, to cut the line:
Then to past the entire line, it is:
To deploy the above code:
In order to go through each line, you use:
Now you loop using X on column CB!
Have fun!
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!
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Sep 22, 2018 at 03:56 AM
Sep 22, 2018 at 03:56 AM
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:-
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.
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.
Srojassg
Posts
6
Registration date
Friday September 21, 2018
Status
Member
Last seen
October 2, 2018
Sep 24, 2018 at 08:49 AM
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!
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Sep 25, 2018 at 03:15 AM
Sep 25, 2018 at 03:15 AM
You're welcome Sergio.
I'm glad that we were able to help.
Cheerio,
vcoolio.
I'm glad that we were able to help.
Cheerio,
vcoolio.
Srojassg
Posts
6
Registration date
Friday September 21, 2018
Status
Member
Last seen
October 2, 2018
Oct 1, 2018 at 10:11 AM
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.
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.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Oct 2, 2018 at 07:26 AM
Oct 2, 2018 at 07:26 AM
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:-
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" !!
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" !!
Srojassg
Posts
6
Registration date
Friday September 21, 2018
Status
Member
Last seen
October 2, 2018
Oct 2, 2018 at 09:47 AM
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.
.......and hey, I used to create a used to name my important files funny names so no one would suspect to open it.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Oct 2, 2018 at 10:59 PM
Oct 2, 2018 at 10:59 PM
You're welcome Sergio. Glad that I was able to help again.
Cheerio,
vcoolio.
Cheerio,
vcoolio.
Sep 24, 2018 at 08:49 AM