I'm a VBA beginner.
I need to copy information in a current Excel file to another file and save as csv. I have 95% of the code working as I want, but one part I can't solve. I've spent the better part of two days experimenting with possible solutions to no avail.
The original Excel files have been created more like a 'Word Document' than an Excel spreadsheet which has created a several challenges:
1. The 'starting row' for the 'table' I need to select and copy is not the same file to file, e.g. the first row of data is on Row 8, or Row 11, or 9, etc.
2. Column 1 and Column 2 of the table can be blank (as this information is optional), so I am using Column 3 which is 'required info' to find the actual 'starting row' for the table and set an "ActiveCell"
3. The number of rows in the table varies from form to form (one could have 49, another 80, another 200, etc.)
This code gets me the three 'pieces' I need:
'Selects the correct cell to find the 'starting row' for the table on every file
Worksheets(1).Select
Worksheets(1).Range("C4").Select
Cells.Find(What:="QTR", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Range("A1").Select
If IsEmpty(ActiveCell.Value) = True Then
ActiveCell.Offset(, 1).Range("A1").Select
End If
' Finds the last row of data in the table after finding the proper 'ActiveCell'
last_QTR_Row = Cells(Rows.Count, 3).End(xlUp).Row
' Selects the required range for the table around the ActiveCell
Range(Cells(Selection.Row, 1), Cells(Selection.Row, 10)).Select
What I can't find the answer for (and have tried for hours with trial and error) is how can I get the 2nd and 3rd elements to work together (or totally take a different approach that works better) to: 1) Select the needed range around the ActiveCell (Column 1: Column 10) and, 2) Select down to the 'last row' of the table as determined by the final cell with data in Column 3.
Any help would be greatly appreciated. I have included a sample file in this post. Thank you so much in advance for your help.
Bookmarks