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

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?

VBA Code:
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,326
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Awesome!!

That worked!!!

Thank you very much, @JoeMo!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,539
Messages
5,548,631
Members
410,861
Latest member
Victor96
Top