Copy/Paste active row(s) range into closed workbook

[mssbass]

I've got the following code but it is only pasting the selected call into the closed workbook. I want the user to be able to select the current row or multiple rows and paste to the next blank row in the closed workbook.


Sub Button2_Click()
Dim FileName As String
Dim wb As Workbook
Dim irow As Long
Dim CopyRange As Range
Dim PasteRange As Range

FileName = "\\abc.com\confidential\Final.xlsx"
irow = ActiveCell.Row
Range(ActiveCell, ActiveCell.Offset(1, 8)).Copy
Application.ScreenUpdating = False

Set wb = Workbooks.Open(FileName, ReadOnly:=False)

'Next empty range in worksheet
With wb.Sheets("FINAL")
Set PasteRange = .Range("A" & .Cells(.Rows.Count, "A").End(xlUp).Row + 1)
End With

'paste values only
PasteRange.PasteSpecial Paste:=xlPasteValues
'save & close workbook
wb.Close True

Application.ScreenUpdating = True

MsgBox "Record Copied To Workbook.", 48, "Record Copied"
End Sub

 

[JLGWhiz]

You can use the Application.InputBox method to allow a user to select a range to copy while the macro is running. See below.

Sub Button2_Click()
Dim FileName As String
Dim wb As Workbook
Dim irow As Long
Dim CopyRange As Range
Dim PasteRange As Range
FileName = "\\abc.com\confidential\Final.xlsx"
irow = ActiveCell.Row
Set CopyRange = Application.InputBox("Use Mouse Pointer to Select The CopyRange, Then Click 'OK'.", "SELECT RANGE TO COPY", Type:=8)
Range(ActiveCell, ActiveCell.Offset(1, 8)).Copy
Application.ScreenUpdating = False
Set wb = Workbooks.Open(FileName, ReadOnly:=False)
 'Next empty range in worksheet
    With wb.Sheets("FINAL")
        Set PasteRange = .Range("A" & .Cells(.Rows.Count, "A").End(xlUp).Row + 1)
    End With
 'paste values only
PasteRange.PasteSpecial Paste:=xlPasteValues
 'save & close workbook
wb.Close True
Application.ScreenUpdating = True
MsgBox "Record Copied To Workbook.", 48, "Record Copied"
End Sub

 

[mssbass]

I want the user to select the row(s) and it paste the whole rows - right now, it's only pasting a few cells

 

[mssbass]

I'd like to say for each row selected (could be multiple rows), take this range (A-AS) and copy it to the new workbook. So the popup comes up and tells the user to select the rows they'd like to upload then click ok. Then the code loops through each "selected" row and copies column A-AS to the next blank row in the closed workbook then lets them know how many rows were copied sucessfully.

 

[JLGWhiz]

I'd like to say for each row selected (could be multiple rows), take this range (A-AS) and copy it to the new workbook. So the popup comes up and tells the user to select the rows they'd like to upload then click ok. Then the code loops through each "selected" row and copies column A-AS to the next blank row in the closed workbook then lets them know how many rows were copied sucessfully.

Good luck on that. It would take some elaborate code to allow varying user input of non contiguous rows or columns and varying sizes of ranges.. I certainly would not want to try an write such code. But if the A:AS were consistent, it could likely be accomplished with a 'For ... Next' loop to walk through the selected rows and copy that range from each row. Not sure it could be done with the Application.InputBox method though.