Excel VBA: Copy/Paste Data Like a Boss [Dealing with Semi-Structured Files]
Data that we want to copy (using Excel VBA macro) may not always be in the right structure. There can be blank rows, columns not in sequence etc. This project will go through how to deal with such scenarios.
Task: We want to copy data from another Excel file and paste it into our current file in tabular format.
#vba #learnvba #excelvba #excel #exceltutorial #exceltips
Approach: We will progressively tackle individual scenarios. At the very end, we will look at a highly distorted file (the boss file!) and see whether we can import it using what we have built so far.
Scenario 1: Copy/ Paste Data as-is
Scenario 2: Blank cells not filled out by user. We need to populate them based on some logic.
Scenario 3: Data may not always start from first row or first column. Learn how to find the data range in any worksheet.
Scenario 4: There are blank or empty rows in between the dats set. Learn how to detect and delete these rows (or columns)
Scenario 5: Some rows or columns may be Hidden. Do we need to do anything here. We'll find out.
Scenario 6: Some rows or columns may be Grouped. Do we need to do anything here. We'll find out.
Scenario 7: Columns are not in the sequence that we expect them to be. We will need to make our code dynamic enough to tackle this case.
Scenario 8: The Boss File. Its a combination of all the distortions we have seen so far. Can we import it using just what we have built so far?
Full code can be found at my blog site:
https://skillsandautomation.com/proje...
Macro file and all data files have been uploaded to Github:
https://github.com/skillsandautomatio...
00:00 Intro
03:46 Video Layout
04:58 Macro Setup
07:24 Import Data (Simple Version)
10:27 Transfer Data (Simple Version)
13:14 Create Main Sub
14:30 Variation 1: Fill in the Blank Dates
18:44 Variation 2: Find the Data Range
22:57 Variation 3: Delete Blank Rows/ Columns
27:20 Variation 4 & 5: Hidden and Grouped Columns
28:31 Variation 6: Columns not in Sequence
33:30 Variation 7: Final Boss
34:12 Loop Over Files
38:31 Note on Error Handling
