200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hi Guys
I have a problem about clearing data for second file is closed when import data from first file is open . the only problem in the code is column A for second file it doesn't clear the whole cells before import data from first file . it keep old numbers in column A for second file . based on first file should be 66 elemnts , but in second file keep old numbers after row 67 should delete that.
any idea to correct this problem ,please?
Hi Leopard
I think the problem is that your offset is wrong in the code section below:
Application.ScreenUpdating = False
ExtBk.Worksheets("REPORT").Cells(1, 1).CurrentRegion.Offset(1, 1).ClearContents
For i = 2 To lRow
ExtBk.Worksheets("REPORT").Cells(i, 1).Value = IntBk.Worksheets("TABLE 1").Cells(i, 1).Value
Nextsince it clears from B2 (not A2) down and right. That leaves column A unchanged in ExtBk. The ClearContents line should instead read:
ExtBk.Worksheets("REPORT").Cells(1, 1).CurrentRegion.Offset(1, 0).ClearContentsHope this fixes things for you.