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
1356
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 11, 2022
- 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.

5 replies

vcoolio
Posts
1356
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 11, 2022
250
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
1356
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 11, 2022
250
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
1356
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 11, 2022
250
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