Help with Pasting Value only

[vg05]

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

Please Login or Register  to view this content.

----------------------------------------------------------------------------------

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,

Thank you so much!

[vcoolio]

Hello vg05,

You'll need it in two lines of code as follows:-

Please Login or Register  to view this content.

You should now get the idea for the other lines of code.

I hope that this helps.

Cheerio,
vcoolio.

[vg05]

Thanks, but I probably should have mentioned that I tried that code already and I get a syntax error when I do.

Any other suggestions I can try?

[Marc L]

Removing the useless :

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 » ! ◄ ◄

[vg05]

Thank you so much!!

[AliGW]

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

[vg05]

Removing the useless :

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?

Thanks!

[AliGW]

Code tags first, please, then you can continue. Thanks.

[vg05]

Ok its done, can you put my post back so I don't have to type it again now.

[AliGW]

Thank you.

[Marc L]

First thanks for the rep' !

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 …

[vg05]

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

Thank you!

[Marc L]

According to your attachment :

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  

[vg05]

According to your attachment :

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  

Thanks a lot Marc! This does work!

[vg05]

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?

Thank you so much!

[Marc L]

So it could be : (v2)

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  

[vg05]

Thanks! unfortunately, an Error Code right here: (Red Text)

Please Login or Register  to view this content.

Edit: Yellow was a bad choice - red is better

[vg05]

I can move the date column (new column A) to a different column (Not A) if it helps to keep the due date column (old column A) where it was....

[Marc L]

As I well works on my side and as I can't guess anything …

[vg05]

Ok thank you.

Since it works on your end, can you save and re attach my sheet with your code on it?

[Marc L]

Attach your workbook with the data where the error raises …

[vg05]

Here you go,

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.

[Marc L]

Try the new version just updated in post #16 …

[vg05]

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?

Thank you!

[Marc L]

After the Dim codeline add a new codeline : If ProtectContents Then Exit Sub …

[jolivanes]

My preference for your problem described in Post #1 is

Please Login or Register  to view this content.

That problem was solved but just for future.