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.

Code:

---


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

---

untested, try:

Code:

---

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.

or you could try using:

Code:

---

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.

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

Code:

---


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

---

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

Not without sight of the workbook.

how do i attach the workbook to this site

http://www.vbaexpress.com/forum/faq....b3_attachments