Consulting

Results 1 to 8 of 8

Thread: copy and paste into next blank cell of table

  1. #1
    VBAX Regular
    Joined
    Nov 2018
    Posts
    15
    Location

    copy and paste into next blank cell of table

    im trying to copy and paste data from one sheet to another to a table that but want it to paste it to the next blank cell and then save the document any tips would help.

    sub CopyRangeFA()
    
    
    
    'Date
      Worksheets("Final Use").Range("D7").Copy
      Worksheets("Log").Range("$D3").PasteSpecial Paste:=xlPasteValues
      Application.CutCopyMode = False
    'Time
      Worksheets("Final Use").Range("D9").Copy
      Worksheets("Log").Range("$E3").PasteSpecial Paste:=xlPasteValues
      Application.CutCopyMode = False
    'SN#
      Worksheets("Final Use").Range("D11").Copy
      Worksheets("Log").Range("$C3").PasteSpecial Paste:=xlPasteValues
      Application.CutCopyMode = False
    'Team Lead
      Worksheets("Final Use").Range("D13").Copy
      Worksheets("Log").Range("$B3").PasteSpecial Paste:=xlPasteValues
      Application.CutCopyMode = False
    
    
    End Sub
    Last edited by Me00550; 04-12-2019 at 06:00 AM.

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,512
    untested, try:
    Sub CopyRangeFA()
    
    nextRow = Worksheets("Log").Cells(Rows.Count, "A").End(xlUp).Row+1
    
    'Date
    Worksheets("Final Use").Range("D7").Copy
    Worksheets("Log").Range("$D" & nextRow).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    'Time
    Worksheets("Final Use").Range("D9").Copy
    Worksheets("Log").Range("$E" & nextRow).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    'SN#
    Worksheets("Final Use").Range("D11").Copy
    Worksheets("Log").Range("$C" & nextRow).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    'Team Lead
    Worksheets("Final Use").Range("D13").Copy
    Worksheets("Log").Range("$B" & nextRow).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    
    End Sub
    The line:
    nextRow = Worksheets("Log").Cells(Rows.Count, "A").End(xlUp).Row+1

    uses column A to determine the next blank row, but that may not be the best column to use.
    Choose a column which (a) has no chance of a blank cell being copied to it from the other sheet and (b) that is within the bounds of the table being added to.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,512
    or you could try using:
    Sub blah()
    nextRow = Worksheets("Log").Cells(Rows.Count, "B").End(xlUp).Row + 1
    With Sheets("Final Use")
      Worksheets("Log").Range("B" & nextRow).Resize(, 4).Value = Array(.Range("D13"), .Range("D11"), .Range("D7"), .Range("D9"))
    End With
    End Sub
    For this to work well, every time a copy is carried out, cell D13 must never be blank.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Regular
    Joined
    Nov 2018
    Posts
    15
    Location
    thank you P45cal the 1st one worked, however is it possible to have 2 worksheets feed the same table and get the 2nd sheet try and find the blank cell next the data the previous sheet copyied. for example:

    with a click of a button Sheet FA copies Cells data to table cells B3:E3
    with click of a button on Sheet QAT copies data to table cells cells F3:H3 at later time

    i got the button code work down pat i just need help with coping and paste

    here is all i code i have so far

    Sub FAButtClick()Call Send_RangeFA
    Call CopyRangeFA
    End Sub
    
    
    Sub QATButtClick()
    Call Send_RangeQAT
    Call CopyQAT
    
    
    
    
    End Sub
    
    
    
    
    Sub Send_RangeFA()
       
       ActiveSheet.Range("B3:E15").Select
       
       ActiveWorkbook.EnvelopeVisible = True
       
       With ActiveSheet.MailEnvelope
          .Introduction = ""
          .Item.To = ""
          .Item.Subject = Range("D11") & " QAT NOTIFICATION TRACKER UNIT COMPLETE"
          .Item.Send
       End With
    End Sub
    
    
    
    
    
    
    Sub Send_RangeQAT()
       
       ActiveSheet.Range("B3:D15").Select
       
       ActiveWorkbook.EnvelopeVisible = True
       
       With ActiveSheet.MailEnvelope
          .Introduction = ""
          .Item.To = ""
          .Item.Subject = Range("C8") & " QAT NOTIFICATION TRACKER QC INSPECTION COMPLETE"
          .Item.Send
       End With
    End Sub
    
    
    Sub CopyRangeFA()
    
    
    nextRow = Worksheets("Log").Cells(Rows.Count, "B").End(xlUp).Row + 1
    
    
    'Date
    Worksheets("Final Use").Range("D7").Copy
    Worksheets("Log").Range("$D" & nextRow).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    'Time
    Worksheets("Final Use").Range("D9").Copy
    Worksheets("Log").Range("$E" & nextRow).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    'SN#
    Worksheets("Final Use").Range("D11").Copy
    Worksheets("Log").Range("$C" & nextRow).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    'Team Lead
    Worksheets("Final Use").Range("D13").Copy
    Worksheets("Log").Range("$B" & nextRow).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    
    
    
    End Sub
    
    
      Sub CopyQAT()
    nextRow = Worksheets("Log").Cells(Rows.Count, "F").End(xlUp).Row + 1
    
    
    'Date
    Worksheets("QAT COMPLETION").Range("C6").Copy
    Worksheets("Log").Range("$F" & nextRow).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    'Time
    Worksheets("QAT COMPLETION").Range("C8").Copy
    Worksheets("Log").Range("$G" & nextRow).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    'NumberOfTags
    Worksheets("QAT COMPLETION").Range("C14").Copy
    Worksheets("Log").Range("$H" & nextRow).PasteSpecial Paste:=xlPasteValues
    
    
    
    
    Application.CutCopyMode = False
      End Sub
      
     
    End Sub

  5. #5
    VBAX Regular
    Joined
    Nov 2018
    Posts
    15
    Location
    after playing with my code i discovered that in order for my data to transfer properly i need the CopyQAT section to look up the existing SN number previously recorded by CopyRangeFA and transfer the new date in the appropriate columns associated with that SN number


    i have tried different Vlookups and cant them to work
    anyone have an ideas ??

  6. #6
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,512
    Not without sight of the workbook.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    VBAX Regular
    Joined
    Nov 2018
    Posts
    15
    Location
    how do i attach the workbook to this site

  8. #8
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,512
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •