JARichard74
Board Regular
- Joined
- Dec 16, 2019
- Messages
- 114
- Office Version
- 365
- Platform
- Windows
Get the error on this line wb.Worksheets("Bids").Range("A1:RZ100").Value = wb2.Worksheets("All").Range("A1:RZ100").Value. Copying a range but could be copying the entire sheet
VBA Code:
Option Explicit
Sub FetchMERX_WF()
Dim wb As Workbook, wb2 As Workbook
Dim ws As Worksheet
Dim vFile As Variant
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim i As Integer
Application.ScreenUpdating = False
Application.AutoCorrect.AutoFillFormulasInLists = False
'Set destination workbook
Set wb = ActiveWorkbook
'Open the source workbook from MERX
If MsgBox("In the next step, select the Excel file you downloaded from MERX", vbOKCancel + vbInformation) = vbCancel Then Exit Sub
vFile = Application.GetOpenFilename("Excel-files,*.xls", _
1, "Select One File To Open", , False)
'if the user didn't select a file, exit sub
If TypeName(vFile) = "Boolean" Then Exit Sub
Workbooks.Open vFile
'Set source workbook
Set wb2 = ActiveWorkbook
'Copy data from the source workbook to destination workbook
wb.Worksheets("Bids").Range("A1:RZ100").Value = wb2.Worksheets("All").Range("A1:RZ100").Value
ActiveWorkbook.Close SaveChanges:=False
Set wb = ActiveWorkbook
wb.Worksheets("Bids").Activate
'Setup the Price sheet
Call Module18.GetBids
' Clears the imported data in the and Bids sheet
ThisWorkbook.Sheets("Bids").Cells.Clear
' Reinstate the protection
ThisWorkbook.Worksheets("Prep").Protect Password:="", Contents:=True
ThisWorkbook.Worksheets("Prep").EnableSelection = xlUnlockedCells
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub