copy and paste into next blank cell of table

[Me00550]

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

[p45cal]

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]

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.

[Me00550]

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

[Me00550]

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

[p45cal]

Not without sight of the workbook.

[Me00550]

how do i attach the workbook to this site

[p45cal]

http://www.vbaexpress.com/forum/faq.php?faq=vb3_reading_posting#faq_vb3_attachments