copy and paste data from a source file into a.......

brumby

Active Member
Joined
Apr 1, 2003
Messages
398
new unique sheet each day......

Hiya again,

Our MRP system pumps out each day an excel sheet showing all of the previous days transactions. Currently I copy and paste this into a file which is a pain. The file which is created always has the same name "tranday.xls"

What I would love to do is each day open the same file which at the push of a button, open up "tranday.xls", copys the data and paste it into a new worksheet named "ddmmyy"
This should hopefully give me a worksheet for each day of all of the transactions that take place.

Can anyone help?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello brumby,
I've got to hold off & do a bit of work here (about an hour) and can write some quick code
for you then if nobody does before that.

Is the file "tranday.xls" in the same folder as the workbook you'll be pasting the data to?
(If not then what's the file path to it?)
What is the name of the sheet in "tranday.xls" that you copy from?
And will there ever be any need for error trapping the current day's new sheet name because
of running this code more than once a day?
 

brumby

Active Member
Joined
Apr 1, 2003
Messages
398
u star.....

Is the file "tranday.xls" in the same folder as the workbook you'll be pasting the data to?
(If not then what's the file path to it?) - no its not. Tranday.xls is in t:\xports\tranday.xls and my file is in j:\materials\summary.xls

What is the name of the sheet in "tranday.xls" that you copy from? - sheet1

And will there ever be any need for error trapping the current day's new sheet name because
of running this code more than once a day - no..........file created covers previous days transactions, and is only run once per day, never duplicating any data

I hope this is good for u

many many thanks
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Yup, that's just what I needed to know.
I went ahead and made it so it can be run multiple times per day. (You never know when
something unexpected will come up requiring it to be run more than once someday.)
What it will do is delete any existing sheet named for the current day (if it already exists)
and replace it with the most current data.

Give this a shot and see if it's what you're looking for.
Code:
Sub CopyDataFrom_tranday()
Dim fPath$, fName$, DestSht$
fPath = "t:\xports\"
fName = "tranday.xls"
DestSht = Format(Date, "ddmmyy")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Sheets(DestSht).Delete
Application.DisplayAlerts = True
Err.Clear
Sheets.Add.Name = DestSht
On Error Resume Next
Workbooks(fName).Activate
If Not Err = 0 Then
  Workbooks.Open fPath & fName
End If
Err.Clear
Workbooks(fName).Sheets("Sheet1").Cells.Copy _
  ThisWorkbook.Sheets(DestSht).[A1]
Windows(fName).Close False
Application.ScreenUpdating = True
End Sub

Let us know how/if this works out.
 

brumby

Active Member
Joined
Apr 1, 2003
Messages
398
Perfect, it works exactly how Id pictured.

Many Many Thanks

brumby
 

Forum statistics

Threads
1,136,307
Messages
5,674,986
Members
419,541
Latest member
freddyboots

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