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

mssbass

Board Regular
Joined
Nov 14, 2002
Messages
235
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,557
Office Version
  1. 2013
Platform
  1. Windows
You can use the Application.InputBox method to allow a user to select a range to copy while the macro is running. See below.

Rich (BB code):
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

Board Regular
Joined
Nov 14, 2002
Messages
235
Platform
  1. Windows
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

Board Regular
Joined
Nov 14, 2002
Messages
235
Platform
  1. Windows
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,557
Office Version
  1. 2013
Platform
  1. Windows
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,114,666
Messages
5,549,311
Members
410,909
Latest member
aa42
Top