Copying unlimited data from on excel to another excel..

Closed
swapnilteli Posts 2 Registration date Thursday January 1, 2015 Status Member Last seen January 2, 2015 - Jan 1, 2015 at 03:21 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Jan 9, 2015 at 06:45 AM
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.
Related:

5 responses

vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Jan 1, 2015 at 07:34 AM
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.
0
swapnilteli Posts 2 Registration date Thursday January 1, 2015 Status Member Last seen January 2, 2015
Jan 2, 2015 at 08:46 AM
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.
0
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Jan 2, 2015 at 05:59 PM
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.
0
I am new here in VBA, I just wanted to copy data from source.xlsx to desination.xlsx. that's it.

Thank you!!!
0

Didn't find the answer you are looking for?

Ask a question
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Jan 9, 2015 at 06:45 AM
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.
0