CODEWITHSUNDEEP
excelvbaloopsmacrosworksheet
Tomás Ayala
Tomás Ayala

Reputation: 107

Copy Paste Worksheets names to another workbook

I'm having trouble to do this. When I start the macro, It doesn't produce any error, but neither throws me any result.

Sub EmpresasCubiertas()


Dim x As Workbook
Dim y As Workbook

'## Open both workbooks first:
Set x = Workbooks.Open("G:\Estudios\Biblioteca\Mercado Accionario Chileno\InsertarEmpresa.xlsm")
Set y = Workbooks.Open("G:\Estudios\Biblioteca\Mercado Accionario Chileno\Procesamiento.xlsm")

For i = 3 To x.Sheets.Count

'Now, paste to y worksheet:

y.Sheets(1).Range(“A” & i) = x.Sheets(i).Name

Next i


End Sub

Note that workbook "y" is the workbook that I open first to run the macro. i.e It is already open, if it is of any help.

Upvotes: 0

Views: 4222

Answers (1)

MrVile
MrVile

Reputation: 26

The issue lies with the missing Value property next to the Range, in your loop. It should read,

y.Sheets(1).Range(“A” & i).Value = x.Sheets(i).Name

I am assuming that you deliberately chose to start copying worksheet names from i = 3. If you need all worksheets, just change the value of i to 1.

Try This: 

Sub CopyWorkBookNames()
Application.ScreenUpdating = False 'To avoid screen flickering

Dim y As Workbook
Dim x As Workbook

Set y = Application.ActiveWorkbook
Set x = Application.Workbooks.Open("FilePathToCopyFrom.xlsx")

'Copy and paste worksheet names, in the workbook running the code, starting from cell A3.
'If You want to paste into cell A1, decrement i in the range below accordingly

For i = 3 To x.Sheets.Count
    y.ActiveSheet.Range("A" & i).Value = x.Sheets(i).Name
Next i

x.Close  'Add SaveChanges Options as needed
Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions