Need Help with easy Excel Macro

[clciccarelli]

Thanks in advance to anyone who has code to do this:

I have this form in Excel format, its the same form it never changes except for the data we capture using it. I have a bunch of these forms in a directory spread out with some other excel files. I need to start in a brand new excel doc, and look through the directory at each excel form I have and copy and paste 5 or 6 cells worth of info on the next empty row on the new worksheet. Then move on to the next found excel file.

The cell contents are known well ahead of time, so I can easily refer to explicit cells for copying if I have a relevant sheet. can anyone help wih a coding snippet....willing to $$$ for the answer.

cLc

 

[Joe Was]

This may help but it is not exactly your solution!

Sub myNewData()
'Standard Module code, Like: Module1.
'Ask for new sheet name and copy all data from MyData to the new sheet.
Dim Message$, Title$, Default$, MySheet$, MyList$, MyTest
Dim Message2$, Title2$, Default2$, MyData$
MyTest = False
MyData = "Sheet1"

'Message, title, and default value.
Message = "Enter a New ""Sheet Name"" to add to this workBook:" ' Set prompt.
Title = "Get Sheet Name!" ' Set title.
Default = "TestSheet" ' Set default.

'Get New Sheet Name.
MySheet = InputBox(Message, Title, Default)

'This adds a sheet and names it "your name" or go's to the sheet inputed if it exists.
'Get all sheets name and test for new sheet name.
For Each ws In Worksheets
If ws.Name = MySheet Then
MyTest = True
End If
Next ws
If MyTest <> True Then
Sheets.Add.Name = MySheet
End If

'This selects your new sheet and moves it after sheet "MyData," which could be any sheet name.
Sheets(MySheet).Select
Sheets(MySheet).Move After:=Sheets(MyData)

'This selects the sheet with the data and its range.
'Message, title, and default value.
Message2 = "Enter the Sheet name to get your data from:" ' Set prompt.
Title2 = "Get Data Sheet Name!" ' Set title.
Default2 = "Sheet1" ' Set default.

'Get Data Sheet Name.
MyData = InputBox(Message2, Title2, Default2)
Sheets(MyData).Select
Sheets(MyData).Range("A1").Select
Sheets(MyData).Range(Range("A1"), Sheets(MyData).Range("A65536").End(xlUp)).Select

'This will copy and paste the data to your new sheet.
Selection.Copy
Sheets(MyData).Select
Sheets(MyData).Range("A1").Select

'Test for existing Data on copy to sheet.
If Sheets(MySheet).Range("A1").Value <> "" Then
Sheets(MySheet).Select
Sheets(MySheet).Range("A1").Select
Sheets(MySheet).Range("A65536").End(xlUp).Offset(1, 0).Select
Else
Sheets(MySheet).Select
Sheets(MySheet).Range("A1").Select
End If
'Paste data from MyData to your new sheet.
ActiveSheet.Paste

'At this point your data will be on the new sheet and selected for the next step.
Sheets(MySheet).Select
Sheets(MySheet).Range("A1").Select
End Sub

Sub NamedRng()
'List all the named ranges in a workbook on a new sheet.
'List the "Range Name" and the "Cell: Sheet - Range"
'Run from Standard module!

Set NewListSheet = Worksheets.Add
i = 1
For Each nmRng In ActiveWorkbook.Names
NewListSheet.Cells(i, 1).Value = nmRng.Name
NewListSheet.Cells(i, 2).Value = "'" & nmRng.RefersTo
i = i + 1
Next
NewListSheet.Columns("A:B").AutoFit

End Sub