Euro5
asked on
VBA copy past excel into existing sheet
I have the code below working, but it pastes the data into the wrong sheet.
The code should open a search box, where the user selects a file.
Then it copies the active sheet from that file, and pastes it into "Net Rates 1" sheet.
However, because the button to run is on the "Data Enter" sheet, THAT is where the copy/paste is going.
I tried to insert code to activate the Net Rates 1 sheet, but I continue to get errors when I try.
It's probably something so simple....
Can anyone help??
The code should open a search box, where the user selects a file.
Then it copies the active sheet from that file, and pastes it into "Net Rates 1" sheet.
However, because the button to run is on the "Data Enter" sheet, THAT is where the copy/paste is going.
I tried to insert code to activate the Net Rates 1 sheet, but I continue to get errors when I try.
It's probably something so simple....
Can anyone help??
Sub ImportRate1()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Sheet As Worksheet
Dim PasteStart As range
Set wb1 = ActiveWorkbook
Set PasteStart = range("A1")
Sheets("Net Rates 1").Select
Cells.Select
Selection.Delete
FileToOpen = Application.GetOpenFilename _
(title:="Please choose Net Rate Sheet 1", _
FileFilter:=".xls (*.xls),")
If FileToOpen = False Then
MsgBox "No File Specified.", vbExclamation, "ERROR"
Exit Sub
Else
Set wb2 = Workbooks.Open(Filename:=FileToOpen)
For Each Sheet In wb2.Sheets
With Sheet.UsedRange
.Copy PasteStart
Set PasteStart = PasteStart.Offset(.Rows.Count)
End With
Next Sheet
End If
wb2.Close
End Sub
ASKER
@sktneer, that does the same but the existing sheet data is not removed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome! THANKS!!
Delete the lines of code which are commented out in the below code and see if this resolves your issue.
Open in new window