[RESOLVED] programming macros in excel

[darwingomez]

I have 20 sheets with data, and I want save some data in another sheets, I have this code :

Windows("FICHAS SINANPE.xls").Activate
    Sheets("001 Elbert Zavaleta Zavaleta").Select
    Range("B6:B14").Select
    Selection.Copy
    Windows("Dbase personal.xls").Activate
    Sheets("Datos Personales").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
        , Transpose:=True
 
Windows("FICHAS SINANPE.xls").Activate
    Sheets("002 Dante Alemán De Lama").Select
    Range("B6:B14").Select
    Selection.Copy
    Windows("Dbase personal.xls").Activate
    Sheets("Datos Personales").Select
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
        , Transpose:=True

This code run, but I have 20 sheets and I want to do a bucle where the sheets are inside the bucle, but I don't know how to do

[si_the_geek]

Hi darwingomez, welcome to VBForums!

I'm a bit confused, what do you mean by bucle?

Do you mean that you want to paste the same data into multiple sheets?

[darwingomez]

when I say bucle, I want to say (for i = 0 to i <20), but I don't know how to do write the code exactly to can do the same for the 20 sheets

[si_the_geek]

Assuming that those are the first 20 sheets in the Workbook, this code should do the trick:

Dim i as Integer
  With Workbooks("FICHAS SINANPE.xls")      
    For i = 1 to 20
      .Activate
      .Sheets(i).Select
      Range("B6:B14").Select
      Selection.Copy
      Windows("Dbase personal.xls").Activate
      Sheets("Datos Personales").Select
      Range("A" & (i+1)).Select
      Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
        , Transpose:=True
    Next i 
  End With

[darwingomez]

Ok

Very fast, tks

Darwin

[RobDog888]

I know it may be solved but as another option you can use the .Copy method for the sheets collection to do a Copy/Paste all in one line without doing any Selection or Activate. Should be faster still.

Workbooks("FICHAS SINANPE.xls").Sheets(i).Copy After:=Workbooks("Datos Personales").Sheets(Sheets.Count)

This will copy each sheet in the FICHAS SINANPE.xls workbook and paste them into the Datos Personales workbook. Now if you dont want to copy the entire sheet then your current method is better.

[Hack]

Moved from ClassicVb.