Hello! First of all let me explain that I do not know VBA code - I have pieced together my code from other forum posts and youtube. So when helping, please give me something I can easily type without changing a ton IF POSSIBLE.
So this code is to help me take some information from 1 worksheet and then send it to another worksheet (in the same workbook) to create a data dump for metric tracking. Here is the code so far:
---------------------------------------------------------------------------------------------------------------
The section of the code where I turned the text to red is where I need help. Currently this code works perfectly for what I need except this one line always pastes the formula, and understandably so, but I need it to be value only. In fact, all of them can be value only...if that helps you help me. But this is the one that I need,
Private Sub CommandButton1_Click() If Sheet1.[A13].Value = "" Then MsgBox "You must enter a Part Number before adding to the log": Exit Sub With Sheet4.Cells(Rows.Count, 1).End(xlUp)(2) .Value = Sheet1.[E13].Value .Cells(1, 2).Value = Sheet1.[A13].Value .Cells(1, 4).Resize(, 2).Value = Sheet1.[C13:D13].Value .Cells(1, 6).Value = Sheet1.[I13].Value .Cells(1, 7).Value = Sheet1.[T8].Value .Cells(1, 8).Value = Sheet1.[R13].Value End With End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
MS-Off Ver
MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
Posts
82,775
Re: Help with Pasting Value only
Administrative Note:
We would very much like to help you with your query, however you need to include code tags around your code.
Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.
Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
Ali Enthusiastic self-taught user of MS Excel who's always learning! Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish. Forum Rules (updated August 2023): please read them here.
Private Sub CommandButton1_Click() If Sheet1.[A13].Value = "" Then MsgBox "You must enter a Part Number before adding to the log": Exit Sub With Sheet4.Cells(Rows.Count, 1).End(xlUp)(2) .Value = Sheet1.[E13].Value .Cells(1, 2).Value = Sheet1.[A13].Value .Cells(1, 4).Resize(, 2).Value = Sheet1.[C13:D13].Value .Cells(1, 6).Value = Sheet1.[I13].Value .Cells(1, 7).Value = Sheet1.[T8].Value .Cells(1, 8).Value = Sheet1.[R13].Value End With End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
I actually have 1 other dilemma that maybe you can help me with. So this code is for 1 row of data. But on my sheet, I can have UP TO 10 rows of data, but more than likely each week I would only use 3-4 rows of data. I think based on your code I am comfortable copying, pasting, and changing the cell values to add additional rows of data into the code; however, what can I type to essentially ignore rows without data? So if I only use 4/10 rows, how do I ignore rows 5-10 without having to constantly edit the code?
what can I type to essentially ignore rows without data? So if I only use 4/10 rows, how do I ignore rows 5-10 without having to constantly edit the code?
As it may depend on the worksheet design so the better is you attach your workbook just following the top page yellow banner …
I've attached the sheet. The tabs to focus on are the "template" and the "data log"
When you click the button on template, that first row of data moves to the data log. I would like all of the rows of data (if filled in) to transfer over, while ignoring the empty rows.
Please let me know if you need anymore information from me
Private Sub CommandButton1_Click() Const N = 8 Dim L&, V(), W, R&, C% L = Application.CountA([A13:A33]): If L = 0 Then MsgBox "You must enter a Part Number before adding to the log": Exit Sub ReDim V(1 To L, 1 To N) L = [A35].End(xlUp).Row + 1 W = Application.Index(Range("A13:R" & L), Evaluate("ROW(1:" & L - 12 & ")"), [{5,1,2,3,4,9,18}]) For L = 1 To UBound(W) Step 2 If W(L, 2) > "" Then R = R + 1: For C = 1 To N: V(R, C) = W(L - (C = 7), C + (C > 6)): Next Next Sheet4.Cells(Rows.Count, 1).End(xlUp)(2).Resize(R, N).Value2 = V End Sub
Private Sub CommandButton1_Click() Const N = 8 Dim L&, V(), W, R&, C% L = Application.CountA([A13:A33]): If L = 0 Then MsgBox "You must enter a Part Number before adding to the log": Exit Sub ReDim V(1 To L, 1 To N) L = [A35].End(xlUp).Row + 1 W = Application.Index(Range("A13:R" & L), Evaluate("ROW(1:" & L - 12 & ")"), [{5,1,2,3,4,9,18}]) For L = 1 To UBound(W) Step 2 If W(L, 2) > "" Then R = R + 1: For C = 1 To N: V(R, C) = W(L - (C = 7), C + (C > 6)): Next Next Sheet4.Cells(Rows.Count, 1).End(xlUp)(2).Resize(R, N).Value2 = V End Sub
Hey Marc, Unfortunately, with this being something new for me at work, I keep getting told to add things, and this newer code I don't quite understand like the old one.
Here is what I now need to do: I need ANOTHER date to be in column A, and I need to shift the rest of the cells to the right. The date will be the worksheet date, also found in N9. And then also I added a column, the "reason for non-attainment" and I need that to also populate over. I've reattached that sheet. Any way that you could help with this? Also, is there a way that you could define the terms of your code to help me understand?
Private Sub CommandButton1_Click() Const N = 8 Dim L&, V(), T$(), W, X, R&, C% L = Application.CountA([A13:A33]): If L = 0 Then MsgBox "You must enter a Part Number before adding to the log": Exit Sub ReDim V(1 To L, N), T(1 To L, 0) L = [A35].End(xlUp).Row + 1 W = [T13:T34] W = Application.Index(Range("A13:R" & L), Evaluate("ROW(1:" & L - 12 & ")"), [{5,1,2,3,4,9,18}]) X = [N9].Value2 For L = 1 To UBound(W) Step 2 If W(L, 2) > "" Then R = R + 1: V(R, 0) = X: T(R, 0) = Cells(L + 12, 20).Text For C = 1 To N: V(R, C) = W(L - (C = 7), C + (C > 6)): Next End If Next With Sheet4.Cells(Rows.Count, 1).End(xlUp)(2).Resize(R, N + 1) .Value2 = V .Columns(16).Value2 = T End With End Sub
When I click the button on either the template page or the 9.27 page nothing happens, but when I press it from template(2) I get the mismatch error and the debugger shows the issue I highlighted.
Yes! You sir, are a genius. Thank you so much for all of your help. I have tried to give you more rep, but it won't let me.
I do have 1 last question, and it shouldn't require anymore code or sharing of sheets.
As I am sure you noticed, my plan is to create a new tab every week to track my clients progress. My client will have access to the sheet as well. Once the week is complete, I would like to "freeze" that week so that nothing can be edited. What I have done so far is protected the sheet and unchecked EVERYTHING. Now, nothing can be edited EXCEPT you can still press the "Add orders to data log" button - is there a way that I can "freeze" that button once I protect the sheet so that it cannot be pressed anymore?
Bookmarks