Need Help with easy Excel Macro

clciccarelli

New Member
Joined
Jan 20, 2005
Messages
1
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

Forum statistics

Threads
1,148,224
Messages
5,745,472
Members
423,953
Latest member
MrC54

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 MrExcel.com.
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 "mrexcel.com".
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
Top