Macro/command button help
Solved/Closed
sillygoose2014
Posts
9
Registration date
Monday December 8, 2014
Status
Member
Last seen
January 9, 2015
-
Dec 8, 2014 at 03:07 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Dec 13, 2014 at 05:33 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Dec 13, 2014 at 05:33 AM
Related:
- Macro/command button help
- Cs 1.6 money command - Guide
- At button - Guide
- Change computer name command line - Guide
- Net send command - Guide
- Csgo speed command - Guide
7 responses
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Dec 9, 2014 at 02:15 AM
Dec 9, 2014 at 02:15 AM
Hello Sillygoose,
I think that I have understood your requirements. You are going from the vertical in the "Data" sheet to the horizontal in the "Pending sheet" and deliberately want some of the columns skipped in the "Pending" sheet. Is this correct? If so, have a look at the following link:-
https://www.dropbox.com/s/ilvmmlsttwdxj7u/SillyGoose.xlsm?dl=0
I've created a rough draft workbook for you to have a play with. In the "Data" sheet, the active cell is A2 (Customer Name). So highlight this cell then click on the button to transfer the data to the "Pending" sheet. The data will be deleted from Column B in the "Data" sheet ready for you to insert new data.
I hope this is what you were wanting.
King regards,
vcoolio.
I think that I have understood your requirements. You are going from the vertical in the "Data" sheet to the horizontal in the "Pending sheet" and deliberately want some of the columns skipped in the "Pending" sheet. Is this correct? If so, have a look at the following link:-
https://www.dropbox.com/s/ilvmmlsttwdxj7u/SillyGoose.xlsm?dl=0
I've created a rough draft workbook for you to have a play with. In the "Data" sheet, the active cell is A2 (Customer Name). So highlight this cell then click on the button to transfer the data to the "Pending" sheet. The data will be deleted from Column B in the "Data" sheet ready for you to insert new data.
I hope this is what you were wanting.
King regards,
vcoolio.
sillygoose2014
Posts
9
Registration date
Monday December 8, 2014
Status
Member
Last seen
January 9, 2015
Dec 9, 2014 at 09:27 AM
Dec 9, 2014 at 09:27 AM
Ok, I may have replied to quickly:)
It is transfering the data, but its doing several things that I am trying to resolve.
1. It puts the data on the next empty row with the associated column. I am trying to get it to all be on the same row as the first empty column? I hope this makes sense.
2. I have some shading and borders and it is wiping them all out. Is there a way where it just puts the values and not overwrite shading and borders?
Here is the macro.
Sub TransferData()
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim x As Integer
Set ws = Worksheets("Data")
Set ws1 = Worksheets("Pending")
'x = ActiveCell.Row
If ActiveCell = [B14] Then
Range("B2").Copy ws1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B3").Copy ws1.Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B4").Copy ws1.Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B5").Copy ws1.Range("D" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B6").Copy ws1.Range("F" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B7").Copy ws1.Range("G" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B8").Copy ws1.Range("H" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B9").Copy ws1.Range("I" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B10").Copy ws1.Range("J" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B11").Copy ws1.Range("L" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B12").Copy ws1.Range("M" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B13").Copy ws1.Range("P" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B14").Copy ws1.Range("Q" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B:B").Delete
End If
End Sub
Thanks for your help vcoolio, you are making my holidays brighter:)
It is transfering the data, but its doing several things that I am trying to resolve.
1. It puts the data on the next empty row with the associated column. I am trying to get it to all be on the same row as the first empty column? I hope this makes sense.
2. I have some shading and borders and it is wiping them all out. Is there a way where it just puts the values and not overwrite shading and borders?
Here is the macro.
Sub TransferData()
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim x As Integer
Set ws = Worksheets("Data")
Set ws1 = Worksheets("Pending")
'x = ActiveCell.Row
If ActiveCell = [B14] Then
Range("B2").Copy ws1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B3").Copy ws1.Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B4").Copy ws1.Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B5").Copy ws1.Range("D" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B6").Copy ws1.Range("F" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B7").Copy ws1.Range("G" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B8").Copy ws1.Range("H" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B9").Copy ws1.Range("I" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B10").Copy ws1.Range("J" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B11").Copy ws1.Range("L" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B12").Copy ws1.Range("M" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B13").Copy ws1.Range("P" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B14").Copy ws1.Range("Q" & Rows.Count).End(xlUp).Offset(1, 0)
Range("B:B").Delete
End If
End Sub
Thanks for your help vcoolio, you are making my holidays brighter:)
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Dec 9, 2014 at 06:58 PM
Dec 9, 2014 at 06:58 PM
Hello again Sillygoose,
The following amended code should solve the formatting issue:-
But now I am confused:-
"1. It puts the data on the next empty row with the associated column. I am trying to get it to all be on the same row as the first empty column? I hope this makes sense. "
Your original post indicated that you wanted to take the vertical data (B2, B3, B4, B5 etc.) from the "Data" sheet and transfer it horizontally (B2,C2,D2,E2, etc.) to the "Pending" sheet with any following data filling in the next available row which the code is doing.
Or, are you saying that you still want the titles (Customer Name, Job Name etc.) vertically in the "Pending Sheet" with the job details being transferred vertically also beside the relevant title with the following job details going into the next column and so on? For example: Customer 1 details go into Column B, Customer 2 details go into Column C etc..
Perhaps, to clarify this, you could upload an example of what you would like. Use a file sharing site such as SpeedyShare or DropBox.
Kind regards,
vcoolio.
The following amended code should solve the formatting issue:-
Sub TransferData()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim ws1 As Worksheet
Set ws = Worksheets("Data")
Set ws1 = Worksheets("Pending")
If ActiveCell = [A2] Then
Range("B2").Copy
ws1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("B3").Copy
ws1.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("B4").Copy
ws1.Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("B5").Copy
ws1.Range("D" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("B6").Copy
ws1.Range("F" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("B7").Copy
ws1.Range("G" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("B8").Copy
ws1.Range("H" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("B9").Copy
ws1.Range("I" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("B10").Copy
ws1.Range("J" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("B11").Copy
ws1.Range("L" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("B12").Copy
ws1.Range("M" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("B13").Copy
ws1.Range("P" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("B14").Copy
ws1.Range("Q" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("B:B").ClearContents
Application.ScreenUpdating = True
End If
End Sub
But now I am confused:-
"1. It puts the data on the next empty row with the associated column. I am trying to get it to all be on the same row as the first empty column? I hope this makes sense. "
Your original post indicated that you wanted to take the vertical data (B2, B3, B4, B5 etc.) from the "Data" sheet and transfer it horizontally (B2,C2,D2,E2, etc.) to the "Pending" sheet with any following data filling in the next available row which the code is doing.
Or, are you saying that you still want the titles (Customer Name, Job Name etc.) vertically in the "Pending Sheet" with the job details being transferred vertically also beside the relevant title with the following job details going into the next column and so on? For example: Customer 1 details go into Column B, Customer 2 details go into Column C etc..
Perhaps, to clarify this, you could upload an example of what you would like. Use a file sharing site such as SpeedyShare or DropBox.
Kind regards,
vcoolio.
Didn't find the answer you are looking for?
Ask a question
sillygoose2014
Posts
9
Registration date
Monday December 8, 2014
Status
Member
Last seen
January 9, 2015
Dec 10, 2014 at 09:14 AM
Dec 10, 2014 at 09:14 AM
I see where the confusion lies.
Out of all the columns of data that get transfered the most import is the first column (Customer Name), and that is going to the next empty row as it is written. The issue I am getting is that sometimes the other columns of data do not have values. So, when I fill out all the info on the vertical "Data" sheet and run the macro the customer name is correctly placed, but since some cells are left empty in the other columns, the data for the rest of it is all over the place. This is my fault in poorly explaining what I was trying to accomplish.
Example:
If the next empty cell is A13 for the Customer name then all that other data should go on row 13. Then once I run the macro to enter another set of data the Next empty cell for the customer name is A14 and all corresponding data would go on that row.
I hope this makes sense. If not, I will do the drop box
Out of all the columns of data that get transfered the most import is the first column (Customer Name), and that is going to the next empty row as it is written. The issue I am getting is that sometimes the other columns of data do not have values. So, when I fill out all the info on the vertical "Data" sheet and run the macro the customer name is correctly placed, but since some cells are left empty in the other columns, the data for the rest of it is all over the place. This is my fault in poorly explaining what I was trying to accomplish.
Example:
If the next empty cell is A13 for the Customer name then all that other data should go on row 13. Then once I run the macro to enter another set of data the Next empty cell for the customer name is A14 and all corresponding data would go on that row.
I hope this makes sense. If not, I will do the drop box
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Dec 10, 2014 at 03:15 PM
Dec 10, 2014 at 03:15 PM
Hello Sillygoose,
I'm begining to understand what you are after. But yes, please upload a sample so that we can totally clarify this for you.
Regards,
vcoolio.
I'm begining to understand what you are after. But yes, please upload a sample so that we can totally clarify this for you.
Regards,
vcoolio.
sillygoose2014
Posts
9
Registration date
Monday December 8, 2014
Status
Member
Last seen
January 9, 2015
Dec 10, 2014 at 05:06 PM
Dec 10, 2014 at 05:06 PM
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Dec 11, 2014 at 01:11 AM
Dec 11, 2014 at 01:11 AM
Hello Sillygoose,
Aha! I wasn't aware that you had a Totals row which makes a difference to Excel's ability to find the next available row within your dataset as the search stops at the totals row. There were also discrepancies between the column/row IDs in your first post.
Just as well you sent a sample, thanks.
Anyway, following is your work book with an amended code. It should all work to plan now. Blanks should no longer be an issue. The active cell is still A2 in the "Data" sheet, so highlight cell A2 after you have completed your entries and then click on the button to transfer data to the "Pending" sheet.
https://www.dropbox.com/s/0wi2vtvy6hjq1hp/Sillygoose%28amended%20again%29.xlsm?dl=0
To save yourself some work, you could also add a simple "sum" formula to the Lvl Totals column so it will sum the three columns I, J & K for you with the "sum" formula that you have in the pending totals row doing the rest for you.
Here's hoping!
Cheers,
vcoolio.
Aha! I wasn't aware that you had a Totals row which makes a difference to Excel's ability to find the next available row within your dataset as the search stops at the totals row. There were also discrepancies between the column/row IDs in your first post.
Just as well you sent a sample, thanks.
Anyway, following is your work book with an amended code. It should all work to plan now. Blanks should no longer be an issue. The active cell is still A2 in the "Data" sheet, so highlight cell A2 after you have completed your entries and then click on the button to transfer data to the "Pending" sheet.
https://www.dropbox.com/s/0wi2vtvy6hjq1hp/Sillygoose%28amended%20again%29.xlsm?dl=0
To save yourself some work, you could also add a simple "sum" formula to the Lvl Totals column so it will sum the three columns I, J & K for you with the "sum" formula that you have in the pending totals row doing the rest for you.
Here's hoping!
Cheers,
vcoolio.
sillygoose2014
Posts
9
Registration date
Monday December 8, 2014
Status
Member
Last seen
January 9, 2015
Dec 11, 2014 at 11:20 AM
Dec 11, 2014 at 11:20 AM
Thanks for the assistance, this works perfectly. I owe you a pint, happy holidays
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
>
sillygoose2014
Posts
9
Registration date
Monday December 8, 2014
Status
Member
Last seen
January 9, 2015
Dec 11, 2014 at 04:53 PM
Dec 11, 2014 at 04:53 PM
Hello again Sillygoose,
Glad I could help. You may like to add the following little line of code to the macro (Its up to you):-
Sheets("Pending").Select
Once you click on the button to transfer the data, the extra line of code will take you directly to the "Pending" sheet.
To add it, go to the bottom of the macro and insert it just above the line
Application.ScreenUpdating = True
All the best,
vcoolio.
Glad I could help. You may like to add the following little line of code to the macro (Its up to you):-
Sheets("Pending").Select
Once you click on the button to transfer the data, the extra line of code will take you directly to the "Pending" sheet.
To add it, go to the bottom of the macro and insert it just above the line
Application.ScreenUpdating = True
All the best,
vcoolio.
sillygoose2014
Posts
9
Registration date
Monday December 8, 2014
Status
Member
Last seen
January 9, 2015
Dec 12, 2014 at 09:40 AM
Dec 12, 2014 at 09:40 AM
Thanks again, vcoolio.This is working perfectly. I was definitely not approaching it correctly.
Let me ask you a few questions since you obviously know more about this then I do, if that is alright.
What you did was help me with our means of looking at our operation functions and getting an estimated time to do said functions. How hard would it be to do the following and is it something I could tackle (meaning is it easier then I think it is and can accomplish it)
- I have a notepad file that has a list of operation numbers, as well as material numbers, that we currently upload into our sales system that gets generated by our design program.
- I have a table of the individual operation numbers and how many of those functions can be done in a hour.
- What I would like to accomplish is a way to take that notepad file and import it into the spreadsheet that I am using, it then looks for those operations numbers and associated functions per hour and puts them on the row of a sheet with it all calculated and ready to go.
Essentially doing the functions of entering the data of that column but doing it electronically without human error and much quicker.
Let me know what you think. I appreciate any and all assistance you have given already.
Regards,
Sillygoose2014
Let me ask you a few questions since you obviously know more about this then I do, if that is alright.
What you did was help me with our means of looking at our operation functions and getting an estimated time to do said functions. How hard would it be to do the following and is it something I could tackle (meaning is it easier then I think it is and can accomplish it)
- I have a notepad file that has a list of operation numbers, as well as material numbers, that we currently upload into our sales system that gets generated by our design program.
- I have a table of the individual operation numbers and how many of those functions can be done in a hour.
- What I would like to accomplish is a way to take that notepad file and import it into the spreadsheet that I am using, it then looks for those operations numbers and associated functions per hour and puts them on the row of a sheet with it all calculated and ready to go.
Essentially doing the functions of entering the data of that column but doing it electronically without human error and much quicker.
Let me know what you think. I appreciate any and all assistance you have given already.
Regards,
Sillygoose2014
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
>
sillygoose2014
Posts
9
Registration date
Monday December 8, 2014
Status
Member
Last seen
January 9, 2015
Dec 13, 2014 at 05:33 AM
Dec 13, 2014 at 05:33 AM
Hello Sillygoose,
The good news is.......yes, you can do it yourself by using an inbuilt Excel function which makes this a fairly simple process.
1) Go to the Data tab and go to the "Get External Data" group. Select "From Text". Your documents library should open in a new window.
2) Select your file and then a "Text Import Wizard" window should open. Step 1: read carefully and make your selection (two little radio buttons). Select your "start import row" in the little box. Leave the adjacent box at MS-DOS. There will be a preview of your file at the bottom of this window. Click NEXT.
3) Step 2: this step will allow you to play with the file set up. Suit yourself here then click NEXT.
4) Step 3: this step is self explanatory. Probably leave the column data format on "General". Depending on your data, you may have to click on "Advanced". Have a look in here and see if you need to do anything (most likely not).
4) Click "Finish" and a little "Import Data" window will open asking where you would like to put your data. Select an option and click OK and your data will be imported.
The above instructions seem a little long winded but it is a really quick process. Have a play with it first on a dummy work sheet just to practice ( but not on your "smoko" break!! ).
Also, just a little refinement to the code I sent through to you the other day (only if you wish to use it):-
We can make the button in the "Data" sheet the active cell if you like rather than A2. By doing this, there only need be one click to transfer the data to the "Pending" sheet. I see that your button sits snugly in cell A16 so go to the code and where you see:-
If ActiveCell = [A2], replace the [A2] with [A16]
Your button can do all the work now. But be aware that if you move the button the macro won't work (unless, of course, you also change the cell reference).
Kind regards,
vcoolio.
The good news is.......yes, you can do it yourself by using an inbuilt Excel function which makes this a fairly simple process.
1) Go to the Data tab and go to the "Get External Data" group. Select "From Text". Your documents library should open in a new window.
2) Select your file and then a "Text Import Wizard" window should open. Step 1: read carefully and make your selection (two little radio buttons). Select your "start import row" in the little box. Leave the adjacent box at MS-DOS. There will be a preview of your file at the bottom of this window. Click NEXT.
3) Step 2: this step will allow you to play with the file set up. Suit yourself here then click NEXT.
4) Step 3: this step is self explanatory. Probably leave the column data format on "General". Depending on your data, you may have to click on "Advanced". Have a look in here and see if you need to do anything (most likely not).
4) Click "Finish" and a little "Import Data" window will open asking where you would like to put your data. Select an option and click OK and your data will be imported.
The above instructions seem a little long winded but it is a really quick process. Have a play with it first on a dummy work sheet just to practice ( but not on your "smoko" break!! ).
Also, just a little refinement to the code I sent through to you the other day (only if you wish to use it):-
We can make the button in the "Data" sheet the active cell if you like rather than A2. By doing this, there only need be one click to transfer the data to the "Pending" sheet. I see that your button sits snugly in cell A16 so go to the code and where you see:-
If ActiveCell = [A2], replace the [A2] with [A16]
Your button can do all the work now. But be aware that if you move the button the macro won't work (unless, of course, you also change the cell reference).
Kind regards,
vcoolio.
