Macro/command button help Solved/Closed

Question

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.

vcoolio · Answer

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.

sillygoose2014 · Answer

Thanks. That nailed it.

sillygoose2014 · Answer

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:)

vcoolio · Answer

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.

sillygoose2014 · Answer

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

vcoolio · Answer

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.

vcoolio · Answer

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.

Macro/command button help

7 responses

Subscribe To Our Newsletter!