View Full Version : copy and paste into next blank cell of table
Me00550
04-12-2019, 05:22 AM
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
04-12-2019, 07:33 AM
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
04-12-2019, 07:47 AM
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
04-15-2019, 05:59 AM
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
04-15-2019, 12:31 PM
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
04-16-2019, 05:41 AM
Not without sight of the workbook.
Me00550
04-16-2019, 05:55 AM
how do i attach the workbook to this site
p45cal
04-16-2019, 06:19 AM
http://www.vbaexpress.com/forum/faq.php?faq=vb3_reading_posting#faq_vb3_attachments
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.