cobalt_27

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

 

Borrowing from the Songs with Great Piano. This thread is all about saxophones (or any brass instrument would be cool). One of my all time favorite haunting riffs comes from Your Latest Trick (Dire Straits). I'm sure a few Dave Matthews Band songs should be up here. What else we got?

I never had any reason to dislike the guy. But, what's the back story here? Why is he loathed so universally here and elsewhere?TIA

Computer/game developers out there, let's get on this stat. I will help finance. Such an incredibly awesome game that I cannot believe, amidst all the crap out there, that this hasn't been done yet. Seems relatively easy, too, and I know there have to be a ton of AA out there that this thing would make kajillions.

Sorry, didn't see story or other thread this week. Lomas is still a jerk.