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

brumby

Active Member
Joined
Apr 1, 2003
Messages
400
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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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
Back
Top