Need Help with easy Excel Macro


New Member
Jan 20, 2005
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.


Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Joe Was

MrExcel MVP
Feb 19, 2002
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).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).Range(Range("A1"), Sheets(MyData).Range("A65536").End(xlUp)).Select

'This will copy and paste the data to your new sheet.

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

'At this point your data will be on the new sheet and selected for the next step.
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

End Sub

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back