I'm trying to slog through a macro and need some help. I've done this before a long time ago, but can't translate my prior model into this one.
I have a folder with a 1000+ excel sheets all formatted exactly the same from which I am trying to extract data and compile the same variables into a single database. Steps are as follows:
1. Open an individual Excel Workbook (the first of 1000+ I want to pull data from) from TARGET_FOLDER
2. Copy worksheet
3. Paste Special (values) into a separate Excel sheet called DATABASE (Sheet 1)
4. Then go to Sheet 2 of DATABASE where I've defined the exact few sets of datapoints (i.e., "=Sheet1B1!", "=Sheet1C1!", etc.) and I want to compile into a database and copy Row 2.
5. Then I go to another Excel Workbook called FINAL where I have columns set up for the variables and want to have Row 2 filled with the data from the first workbook and then have 1000+ rows of data compiled for all subsequent workbooks from the TARGET_FOLDER.
So far, what's below is executing the first Excel Workbook just fine. After that, it's all wonkiness, and I don't know how to instruct the macro to follow up and take the next target file, wash, rinse, and repeat to get all files into sequential rows on the FINAL Excel Workbook.
Please help. I know this should be an easy fix, but I can't figure it out.
Sub Macro8() ' ' Macro8 Macro ' Dim i As Long For i = 2 To 1000 ' Application.WindowState = xlMinimized Workbooks.Open Filename:= _ "svr-fileusersDesktopTARGET_FOLDERTARGET_DATA_SHEET.xls" Range("A16:D30").Select Selection.Copy Windows("DATABASE.xlsm").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Sheet2").Select Rows("2:2").Select Application.CutCopyMode = False Selection.Copy Windows("FINAL.xlsx").Activate Range("A2" & i).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Windows("DATABASE.xlsm").Activate Sheets("Sheet1").Select Range("A1" & i).Select Next End Sub