I am trying to create a macro/VBA code that will find a specifically named file in a specific directory and copy out the data located on two worksheets there.
E.g.
Directory:
T:\abc\def\fileDirectory
Filename:
CodeOutput.exe
Worksheet names to be copied over:
A
B
I want all the data to be copied over from worksheet 'A' and pasted onto worksheet 'A' in the Excel file with this VBA code.
Same for 'B' pasted onto 'B.'
And to finally close the originally file and save the current file we are in.
Anyone know how to do this or if this is even possible?
Hello GeneralShamu,
Welcome to the Forum!
Yes, it is possible. Will the directory and file always be the same or do you need to select them both?
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
The file naming convention and directory will always be the same.Originally Posted by Leith Ross
Though there's also a way to let someone select it after hitting a macro button? Intriguing...
Hello GeneralShamu,
The macro can provide the user with a dialog to select a different folder and file if needed.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Star3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Hello GeneralShamu,
One more question, do you want the data to be appended to the sheets of the main workbook or to overwrite the existing data?
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Star3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Hello GeneralShamu,
This macro will append the data from the selected workbook to the workbook with the macro. You will need to add the file path (directory) and file name with the extension to the code. Additionally, you may need to change the worksheet names form "Sheet1" and "Sheet2".
Sub CopyData() Dim DstWkb As Workbook Dim DstWks As Worksheet Dim Filename As String Dim Filepath As String Dim Rng As Range Dim ThisDir As String Dim WksA As String Dim WksB As String Dim X As Variant Filepath = "" Filename = "" WksA = "Sheet1" WksB = "Sheet2" ThisDir = CurDir CurDir = Filepath On Error Resume Next X = Application.Dialogs(xlDialogOpen).Show(Filename) If Not X Then Exit Sub If Err <> 0 Then Exit Sub CurDir = ThisDir Set DstWkb = ThisWorkbook Set DstWks = DstWkb.Worksheets(WksA) Set Rng = DstWks.UsedRange Set Rng = DstWks.Cells(Rng.Rows.Count + 1, "A") Worksheets(WksA).UsedRange.Copy Rng Set DstWks = DstWkb.Worksheets(WksB) Set Rng = DstWks.UsedRange Set Rng = DstWks.Cells(Rng.Rows.Count + 1, "A") Worksheets(WksB).UsedRange.Copy Rng ActiveWorkbook.Close SaveChanges:=False ActiveWorkbook.Save End Sub
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Star3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Sorry had to step away from the desk for a bit.
The macro would overwrite the existing data.
The user interface sounds intriguing but this is something that must be run several times a day with the same file/directory so it'd do itself a disservice unfortunately.
Hello GeneralShamu,
This version will overwrite the data. Again, change the need varaibles to what you are using.
Sub CopyData2() Dim Filename As String Dim Filepath As String Dim WksA As String Dim WksB As String Filepath = "" Filename = "" WksA = "Sheet1" WksB = "Sheet2" Filepath = IIf(Right(Filepath, 1) <> "\", Filepath & Filepath, Filepath) Workbooks.Open Filepath & Filename Worksheets(WksA).UsedRange.Copy ThisWorkbook.Worksheets(WksA).UsedRange Worksheets(WksB).UsedRange.Copy ThisWorkbook.Worksheets(WksB).UsedRange ActiveWorkbook.Close SaveChanges:=False ActiveWorkbook.Save End Sub
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Star3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Thank you, I got this to work. The only issue I have noticed is that it is not pasting the data into cell A1 on worksheets WksA and WksB. Adding on ".Range("A1")" doesn't fix the problem.
Nevermind, I got it:
Thank you again for all this; it's remarkable how fast you got it!Worksheets(WksA).UsedRange.Copy ThisWorkbook.Worksheets(WksA).Range("A1") Worksheets(WksB).UsedRange.Copy ThisWorkbook.Worksheets(WksB).Range("A1")
By the way, there was an error in the code:
Filepath = IIf(Right(Filepath, 1) <> "\", Filepath & Filepath, Filepath)
should be
Filepath = IIf(Right(Filepath, 1) <> "\", Filepath & "\", Filepath)
Hello GeneralShamu,
Sorry about the typo. I am glad you figured it out and got it running.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Star3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks