Solved: Copy, Open, Paste

[coserria]

Good day to you all,

Here is my quandary,

I am using a web based database, It allows me to export data to excel. I have filtered or searched for this data. So the export is relatively small. 1-100 rows. The data runs from column A to AL. I have code that copies the data I want to the work book that I want. But I can't make it save and I'm not sure that if the next set of data I look at will be pasted below the first set that I already copied to the Destination Workbook. I'm looking to make sure that the, Code I have is correct. And I need to be able to save and close the destination work book each time I use the macro.


Sub CopyRow()
Selection.EntireRow.Copy
Workbooks.Open ("C:\Bin Label\Bin Data.xlsx")
Selection.End(xlUp).Select
ActiveSheet.Paste
End Sub


Source Data = Asset Search

Destination workbook = Bin Data

[patel]

Do you want overwrite the last row or append below ?
Sub CopyRow()
Selection.EntireRow.Copy
Workbooks.Open ("C:\Users\andrea\Desktop\Bin Data.xlsx")
LR = Cells(Rows.Count, "A").End(xlUp).Row ' overwrite
LR = Cells(Rows.Count, "A").End(xlUp).Row +1 ' append
Cells(LR, 1).PasteSpecial
End Sub

[snb]

Sub datatransfer_snb()
With Workbooks.Open("C:\Users\andrea\Desktop\Bin Data.xlsx").sheets(1)
.rows(.cells(rows.count,1).end(xlup).row+1)=selection.entirerow.value
end with
end sub

[coserria]

Good the first works great:clap: :clap: :clap: , copies the rows selected, The second reply copies the first row only. The next piece that I have not been abel to find is how to make the workbook save and close so I do nto have to go to it to save and close. this way I can create a log of the work that was performed and also use it for work on the access program that I have that is using this data to make labels and other documents from the data. I have looked at all the code I have and I think it shoudl be liek this.

Workbooks.save ("C:\Bin Label\Bin Data.xlsx").close


Sub CopyRow()
Selection.EntireRow.Copy
Workbooks.Open ("C:\Bin Label\Bin Data.xlsx")
'LR = Cells(Rows.Count, "A").End(xlUp).Row ' overwrite
LR = Cells(Rows.Count, "A").End(xlUp).Row +1 ' append
Cells(LR, 1).PasteSpecial
Workbooks.save ("C:\Bin Label\Bin Data.xlsx")
Workbooks.close ("C:\Bin Label\Bin Data.xlsx")
End Sub

It might be a bit bulky but that is what I belive should do the trick. never mind after i posted I tested and no workie.

[patel]

Sub CopyRow()
Selection.EntireRow.Copy
With Workbooks.Open("C:\Bin Label\Bin Data.xlsx")
' LR = .Cells(.Rows.Count, "A").End(xlUp).Row ' overwrite
LR = .Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1 ' append
Cells(LR, 1).PasteSpecial
.Close Save = False
End With
Application.CutCopyMode = False
End Sub

but snb macro is more elegant

Sub datatransfer_snb()
With Workbooks.Open("C:\Users\andrea\Desktop\Bin Data.xlsx").Sheets(1)
.Rows(.Cells(Rows.Count, 1).End(xlUp).Row + 1) = Selection.EntireRow.Value
.Close Save = False
End With
End Sub

[coserria]

Patel that got it. Thank you.

[snb]

Sub datatransfer_snb()


With Workbooks.Open("C:\Users\andrea\Desktop\Bin Data.xlsx").Sheets(1)

.Rows(.Cells(Rows.Count, 1).End(xlUp).Row + 1).resize(selection.rows.count)= Selection.EntireRow.Value
.Close True
End With
End Sub