Macro/command button help [Solved/Closed]

Report
Posts
9
Registration date
Monday December 8, 2014
Status
Member
Last seen
January 9, 2015
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
Hello,

I am in need of some help here and would greatly appreciate it. Here is what I am trying to accomplish. I have a basic excel file with two sheets, one is Data the other is Pending. What I am trying to accomplish is make button that can take the data that gets filled in on the Data sheet and place them in the next empty row in their correct locations on the Pending sheet. After the button is clicked I would like it to erase and reset itself. I hope this makes sense.

I don't see where I can attach the file so I will do my best to show you on here

Data Sheet

Customer Name B2
Job Name B3
SO # B4
Line B5
Shorts B6
Pieces B7
Lvl 1 B8
Lvl 2 B9
Lvl 3 B10
Rings B11
Strut B12
Location B13
Weight B14

Pending Sheet

Customer Name A2
Job Name B2
SO # C2
Line D2
Shorts F2
Pieces G2
Lvl 1 H2
Lvl 2 I2
Lvl 3 J2
Rings L2
Strut M2
Location P2
Weight Q2



Thanks for any assistance.

7 replies

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
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.

Thanks. That nailed it.
Posts
9
Registration date
Monday December 8, 2014
Status
Member
Last seen
January 9, 2015

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:)
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello again Sillygoose,

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.
Posts
9
Registration date
Monday December 8, 2014
Status
Member
Last seen
January 9, 2015

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
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
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.
Posts
9
Registration date
Monday December 8, 2014
Status
Member
Last seen
January 9, 2015

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
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.
Posts
9
Registration date
Monday December 8, 2014
Status
Member
Last seen
January 9, 2015

Thanks for the assistance, this works perfectly. I owe you a pint, happy holidays
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213 >
Posts
9
Registration date
Monday December 8, 2014
Status
Member
Last seen
January 9, 2015

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.
Posts
9
Registration date
Monday December 8, 2014
Status
Member
Last seen
January 9, 2015

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
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213 >
Posts
9
Registration date
Monday December 8, 2014
Status
Member
Last seen
January 9, 2015

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.