Ottsel
Board Regular
- Joined
- Jun 4, 2022
- Messages
- 177
- Office Version
- 365
- Platform
- Windows
I'm trying to throw together a macro that'll important data automatically from other workbooks. Currently, I have to open the workbook, copy and PasteSpecial Paste:=xlPasteValues values into the designated cells, close the workbook, then search for the next one.
I believe I can throw together the copy and paste section, but I'm struggling with getting to the first part
Here's the file pathway I'll always be searching:
G:\Jobs\"info in cell G1 contains this folders name"\"info in cell A1 contains this folders name"\"LOT*" & "range*"
* LOT: every excel file begins with "LOT" followed by a number
* Range: A2:A10 will contain the number of the lot I'm attempting to extract data from.
For the range approach I was thinking of using an inputbox that'll have the user select what is needed to be imported.
So I have 2 problems total:
1. How to format the pathway to pickup the cell data within the worksheet - it'll be the activesheet when ran.
2. How to add the number range for the file into an index or lookup somehow.
This being the most challenging part of this start up. After the file is opened, I'll insert a copy and paste code for the data I need. It'll insert the data into the sheet. Here's an example:
The idea is to open the workbook using the custom file pathway, extract what is needed into row B, C, D, E, & F, then close that workbook and proceed onto the next workbook until no one workbooks can be selected.
Any tips, advice, different approaches or code to test would be greatly appreciated.
I believe I can throw together the copy and paste section, but I'm struggling with getting to the first part
Here's the file pathway I'll always be searching:
G:\Jobs\"info in cell G1 contains this folders name"\"info in cell A1 contains this folders name"\"LOT*" & "range*"
* LOT: every excel file begins with "LOT" followed by a number
* Range: A2:A10 will contain the number of the lot I'm attempting to extract data from.
For the range approach I was thinking of using an inputbox that'll have the user select what is needed to be imported.
VBA Code:
'Idea for multiple lot numbers AKA the RANGE
Dim rng As Range
Set rng = Application.InputBox("Select which lots will be imported:", "Test, Inc", Type:=8)
So I have 2 problems total:
1. How to format the pathway to pickup the cell data within the worksheet - it'll be the activesheet when ran.
2. How to add the number range for the file into an index or lookup somehow.
This being the most challenging part of this start up. After the file is opened, I'll insert a copy and paste code for the data I need. It'll insert the data into the sheet. Here's an example:
Builder Differences - PROJECT_ALPHA.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | CMT | PC | ADDRESS | PLAN | ELEV | TEST | BZR | ||
2 | 1020 | ||||||||
3 | 1021 | ||||||||
4 | 1025 | ||||||||
5 | 1028 | ||||||||
6 | 1030 | ||||||||
7 | 1100 | ||||||||
8 | 1101 | ||||||||
9 | 1102 | ||||||||
10 | 1103 | ||||||||
WAC |
The idea is to open the workbook using the custom file pathway, extract what is needed into row B, C, D, E, & F, then close that workbook and proceed onto the next workbook until no one workbooks can be selected.
Any tips, advice, different approaches or code to test would be greatly appreciated.