Copy/Paste between two worksheets

[rollingzep]

Hi,

I import a text file to my FX Worksheet in my Workbook, WBFX.

I then insert a row at the very top of my FX Worksheet. I then copy the header from another worksheet, EX which is the same Workbook.

I need to paste it to the top row of my FX Worksheet.

Currently, I am doing this manually. I need help with coding to insert a row, copy header from the second sheet, select the top row of the first sheet and paste the header row.

How to do this?

Also, the weekly text file I import is dated. While the name is same, the date changes every week. (example - xxxx_FX_04182002).
I have currently hardcoded the text file.

Can I automate the date part?

Thanks

 

[Joe4]

Currently, I am doing this manually. I need help with coding to insert a row, copy header from the second sheet, select the top row of the first sheet and paste the header row.

You should be able to get most of the code you need for this using the Macro Recorder.
Simply turn on your Macro Recorder, and record yourself performing these steps manually, then stop the Macro Recorder.
Then go into the VB Editor and look at the code you just recorded. You should be able to copy/paste this into your VBA procedure, and it should do what you want.

 

[rollingzep]

You should be able to get most of the code you need for this using the Macro Recorder.
Simply turn on your Macro Recorder, and record yourself performing these steps manually, then stop the Macro Recorder.
Then go into the VB Editor and look at the code you just recorded. You should be able to copy/paste this into your VBA procedure, and it should do what you want.

I was able to use the macro recorder and modify the code to suit my req.

But I need help with the below.
The weekly text file I import is dated. While the name is same, the date changes every week. (example - xxxx_FX_04182002).
I have currently hardcoded the text file.
Can I automate the date part?

 

[Joe4]

I was able to use the macro recorder and modify the code to suit my req.

But I need help with the below.
The weekly text file I import is dated. While the name is same, the date changes every week. (example - xxxx_FX_04182002).
I have currently hardcoded the text file.
Can I automate the date part?

If you post the VBA code that you have with that hard-coded file name, we can probably help you modify it to do what you need.
Please be sure to post your code as described here: How to Post Your VBA Code

 

[rollingzep]

It's simple

strfilename = "P:\FX_Reports\xxxx_FX_04182002.txt"
strDelimiter = "|"

intTextFile = FreeFile
Open strFileName For Input As intTextFile
strTextFileContent = Input(LOF(intTextFile), intTextFile)

Close intTextFile

 

[Joe4]

OK, what is the logic for the date used in the filename that you will be looking for?
Will the date always be the MONDAY of that week?

And when will you be running this code, compared to the date in the filename?
Will you always be running it on Monday (same day) or Tuesday (next day)?
Or could you be running it on any date of that week?

 

[rollingzep]

I apologize for not providing details. I am new to this team
It is an quarterly file. The last was xxxx_FX_20220331. txt
We run it the next day.

 

[Joe4]

If you want to return the file with date in the name from the previous day, you can use this to set the file name:

strfilename = "P:\FX_Reports\xxxx_FX_" & Format(Date - 1, "mmddyyyy") & ".txt"