copying unlimited data from on excel to another excel.. Closed

Question

Hi All,

I want to copy all the data from one excel sheet to another excel on load event (when I open) of destination excel.

could you please help me providing template or code for the same?.

Assistance will be appreciated.

Thanks.

vcoolio · Answer

Hello Swapnilteli,

The following code may be what you are wanting:-

Private Sub Workbook_Open()
Dim NewSheetName As String

Sheets("Sheet1").Copy Before:=Worksheets("Sheet2")
  NewSheetName = InputBox("Hi Swapnilteli. Please name your new Worksheet.")
  On Error Resume Next
  ActiveSheet.Name = NewSheetName
  
End Sub

As you requested, this code needs to be inserted as a Workbook_Open event so that the code will activate as soon as you open the workbook. On sheet1, right click on the sheet1 tab, click view code. The VBE field will open. Click on "This Workbook" over on the far left. The code field will turn white. Paste the above code into the field.

The only downside here is that every time you open the workbook, a new sheet will be created which you may not want. You can right click on the unwanted sheet tab and select "Delete".

(Then again, a forum expert, if watching this thread, may have a different idea).

You can have a look at my test workbook here and have a play with it yourself.

https://www.dropbox.com/s/2ucnu5cwc86oc5p/Swapnilteli%20Test.xlsm?dl=0

Kind regards,
vcoolio.

swapnilteli · Answer

Thanks Vcoolio...

But, the above code copies data in single sheet (i.e. sheet to sheet).   however, i want to copy data from one excel to another excel, which would be saved at different locations.

Could you please provide me the code for same.

Your assistance will be appreciated.

Thanks in advance.

vcoolio · Answer

Hello Swapnilteli,

Its not clear exactly what you would like. Please upload a sample of your workbook so we can see what you are trying to do. You can use a file sharing site such as DropBox.

Please desensitise any confidential data.

regards,
vcoolio.

swapnilteli · Answer

I am new here in VBA, I just wanted to copy data from source.xlsx to desination.xlsx. that's it.

Thank you!!!

vcoolio · Answer

Hello Swapnilteli,

The following code will copy all data  from an existing workbook to a new workbook:-

Private Sub Workbook_Open()

Dim nWB As Workbook
  Dim WB As Workbook
  Dim WS As Worksheet
  Set nWB = Workbooks.Add

For Each WB In Application.Workbooks
  If WB.Name <> nWB.Name Then
  For Each WS In WB.Worksheets
WS.Copy After:=nWB.Sheets(nWB.Worksheets.Count)
Next WS
End If
Next WB

Application.DisplayAlerts = False
nWB.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete
nWB.Application.DisplayAlerts = True

End Sub

It will actually create the new workbook.

Kind regards,
vcoolio.

Copying unlimited data from on excel to another excel..

5 responses

Similar discussions

Subscribe To Our Newsletter!