How do I use vba or a macro to rename an imported worksheet

[sidtaylor]

I need to import a text file to excel. I want to take that worksheet and rename it for yesterday's date. I then need to be able to sum up information from all of the dates in a month to date summary. Thanks in advance for any help you can give me.

 

[Zathras]

You didn't give us very much to work with.

What is the format of your text file?
What is the format of your worksheet?
How do you know which items from the text to add to which cells in the worksheet?
Are you keeping each day's sheet in a separate tab of one workbook or in different workbooks, or are you keeping the worksheet version of the data at all, after doing your sums?

Assuming the text file has some structure, one approach would be to have a database in the spreadsheet with one column to identify the date. Then to process a new day's data, the procedure would be to add rows to the database using information from the text file. Finally, the month-to-date summary reports would be generated using database formulas (e.g. DSUM)

With a little more detail, someone here should be able to help you accomplish your goal.

 

[SARUN]

Your objective can be acheived but you're anot clear which part you need help.

1. Import of Text File to Excel

Sample Code:

Workbooks.OpenText FileName:=Trim(fpname), Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, _
1), Array(31, 1), Array(72, 4), Array(83, 1), Array(92, 4), Array(101, 4), Array(115, 1), _
Array(128, 1), Array(136, 1), Array(142, 1), Array(149, 2), Array(171, 4), Array(179, 2), _
Array(201, 4), Array(209, 1), Array(217, 1), Array(243, 1), Array(254, 1), Array(265, 1), _
Array(276, 1), Array(287, 1), Array(298, 9))

2. Rename Sheet Name to Yesterday's Date

Dim RptDt As Variant
RptDt = Format(Date - 1, "mmddyy&quot
Sheet1.Name = RptDt

3. Summary Report

I would suggest you to use Pivot Table Report

Hope I could be of Help... "Whereever you go there are people who need you for what you can do..."

 

[SARUN]

Oops, I'm not sure about using Pivot Tables if you've data on different sheets.

But the approach depends upon what data you have and what summary you're looking for. "Whereever you go there are people who need you for what you can do..."