Copy and Pasting Data to a New Workbook using VBA

[nishansingh]

First time posting I apologize in advance if question is not formatted clearly or if this post is unorthodox in any way.


This is my first time using VBA so I am not familiar with the language. I have a work assignment for which I need to copy and paste the data from several excel files (All in Same Folder) together into one new master sheet. All of the excel files have the same format. I need to copy 2 tabs from each excel worksheet, "Raw Data" and "RBC Raw Data" respectively.

So far I have figured out how to copy and paste the data from only one tab of the worksheet. Also I have done this by directly entering the path of the worksheet into my code rather then the path of the folder in which all files exist.
(Was trying to figure out how to do one file before I try making a loop that runs through all files.)

any feedback or tips are highly appreciated.

THANK YOU.

This is my code so far:
-----------------------------------------------------------------------------------------------------------------------------

Sub OpenWorkbookToPullData()


Dim path As String
path = "\\ntdisk01\imo\IMO\Valuations\TOR Credit IPV\CDS Corporates\201506\CDS File-Corporates-Jul 2.xlsm"






Dim currentWb As Workbook
Set currentWb = ThisWorkbook


Dim openWb As Workbook
Set openWb = Workbooks.Open(path)


Dim openWs As Worksheet
Set openWs1 = openWb.Sheets("Raw Data")




Set SR1 = openWs1.Range("A1:XFD1048576")

SR1.Select
Selection.Copy

currentWb.Activate
Range("A1:XFD1048576").Select

ActiveSheet.Paste



openWb.Close (False) 'closes the note book that was opened


End Sub

 

[farmerscott]

Nishan,

I am not sure if I can solve all your problem, but can get you started.

Can you clarify that you want -

1. to copy the whole sheet (with same name) to the new workbook
Note: Excel will not let you have sheets with the same name. So you need to think of some other naming.


or

2. just parts of the sheet.

Have a look at these and tell us if this is something like your problem.

http://www.mrexcel.com/forum/excel-...array-copy-dynamic-range-sheets-new-book.html

vba - For Each Function, to loop through specifically named worksheets - Stack Overflow

FarmerScott

 

[nishansingh]

I hope this made my question easier to understand. Thank you.

I need the macro to open the file and copy and paste all data from 2 of the 3 tabs.
Right now it is copy and pasting from only 1 tab. (called "Raw Data")

Need help having the macro copy information from both tabs.
How would I have the macro copy and paste all data from second tab (called "RBC Raw Data") and place it directly under the data from first tab.