Import not working as expected? Interacting with an imported sheet the 2nd time...

[Mr2017]

Hi

I've got an import macro which successfuly gets data from column A in a file to column A in another file.

But it fails when I want to go back to get data from columns B - F of that file, then paste that data in column C - G of the new file.

I must give credit to @offthelip for helping me to get the first part working.

I'm posting this separately, in case he doesn't see it. It's also a slightly different question:

Any thoughts would be greatly appreciated - the part where it fails where is says iWB.activate after the comment: "'get columns b - f from the Prod Entry Sheet"

I thought that that code would re-activate the imported file?

Sub GetProdData()


Dim fd As FileDialog
Dim filewaschosen As Boolean
Dim Report As Workbook
Dim iWB As Workbook



Set Report = ActiveWorkbook
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Filters.Clear
fd.Filters.Add "Custom Excel Files", "*.xlsx, *.xlsm, *.xls"
fd.AllowMultiSelect = False
fd.InitialFileName = Environ("UserProfile") & "\Box\Proposals & Contracts"
filewaschosen = fd.Show
fd.Execute

'ProductEntrySheet.Select
Worksheets("Product Entries Sheet").Select
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A" & lastrow).Copy
Report.Activate
Input1.Activate
Range("A1:A" & lastrow).PasteSpecial



'get columns b - f from the Prod Entry Sheet
iWB.Activate
Worksheets("Product Entries Sheet").Select
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("B2:F" & lastrow).Copy
Report.Activate
Input1.Activate
Range("C2:G" & lastrow).PasteSpecial

 

[JoeMo]

I've only looked briefly at your code so you may want to try this on a copy of your Report workbook. It appears that iWB has never been set so the last block aimed at copying b-f may encounter an error.

Try adding this line after the line fd.Execute
Set iWB = Activeworkbook

 

[Mr2017]

Awesome!!

That worked!!!

Thank you very much, @JoeMo!

 

[JoeMo]

Awesome!!

That worked!!!

Thank you very much, @JoeMo!

You are welcome - thanks for the reply.