0

I'm trying to copy G38:K38 from the 7th to the 18th sheet of the source file (Tool) and paste it to the master file (30-Day).

Here are what I've tried so far, please give the newbie in VBA some advices.

Sub macro1()
Windows("Tool.xlsm").Activate
    Dim j As Integer
    For j = 7 To 18
    Sheets(j).Select
     'My first thought is to select sheet by index number, 
     'and set the index number as variables, but it doesn't work...
        Range("G38:K38").Select
        Selection.Copy

Windows("30-Day.xlsm").Activate
    Sheets("SB12").Select
    For k = 17 To 28
        Range("E" & k).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Next k

Next j
End Sub

I've also tried this but it doesn't work either.

Sub macro2()
Windows("Tool.xlsm").Activate
    If ActiveSheet.Index = Worksheets.Count Then
      Worksheets(1).Select
      Else
      ActiveSheet.Next.Select
    End If
        Range("G38:K38").Select
        Selection.Copy

Windows("30-Day.xlsm").Activate
    Sheets("SB12").Select
    For k = 17 To 28
        Range("E" & k).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Next k
End Sub
Astar
  • 1
  • Try `Sheets(ActiveSheet.Index + 1).Select` – M.Douda Jul 23 '18 at 08:11
  • 1
    **(1)** Don't use `windows`, use `workbooks` to access a workbook.. **(2)** No reason to work with `Activate` and `Select`, read https://stackoverflow.com/a/10717999/7599798 **(3)** Why do you use Copy&Paste if you can simple use the `value` of the cells? – FunThomas Jul 23 '18 at 08:20
  • @M.Douda I tried this before and failed. I'll edit the post later and maybe you can help me fiqure out what's wrong with that, thanks. – Astar Jul 23 '18 at 08:27
  • 1
    @FunThomas I did this by recording macro ><" I will try what you mentioned, thanks. – Astar Jul 23 '18 at 08:45

0 Answers0