Copy Paste Macro

**PONJO** · 04-08-2013, 04:27 AM

Please assist, i have a macro that works perfectly fine with one workbook (ADH Feb'13 new WorkfileDownload.xlsm) but refuses to ran with the other ones (ILA Feb'13 WorkfileDownload.xlsm, CBH Feb '13 WorkfileDownload.xlsm, etc).

The problem i figured out is that may be i Dim only one file name, so how do i strFileFrom to include every open/active workbook.

Sub Macro1()
Dim strFileFrom As String
Dim strFileTo As String

strFileFrom = "ADH Feb'13 new WorkfileDownload.xlsm"
strFileTo = "Template.xlsx"

Windows(strFileFrom).Activate
Sheets("Top 20 Line Entries").Select
Range("B3:B22").Select
Application.CutCopyMode = False
Selection.Copy
Windows(strFileTo).Activate
Sheets("Top 20 Line Entries").Select
Range("B14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

**AB33** · 04-08-2013, 04:50 AM

Ponjo,
Please use code tags with your code.
You can use active workbook, so that as long as you are on that book, it should work.
If you are opening a workbook which is not open, you need to change the syntax and use open file

**PONJO** · 04-08-2013, 05:23 AM

HI AB33 i did change to active workbook but now am getting some of my code below highlighted in yellow (example: Sheets("Top 20 FM").Select)

Sub Macro1()

Dim strFileFrom As String
Dim strFileTo As String
 
strFileFrom = ActiveWorkbook.Name
strFileTo = "Template.xlsx"

    Windows(strFileFrom).Activate
    Sheets("Top 20 Line Entries").Select
    Range("B3:B22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Sheets("Top 20 Line Entries").Select
    Range("B14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows(strFileFrom).Activate
    Range("D3:D22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Range("D14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E14").Select
    Windows(strFileFrom).Activate
    Range("F3:F22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Windows(strFileFrom).Activate
   Sheets("Top 20 FM").Select
    Range("B3:B22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Sheets("Major Fund Managers").Select
    Range("B14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows(strFileFrom).Activate
    Range("D3:D22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Range("D14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows(strFileFrom).Activate
    Range("F3:F22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Range("E14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B40").Select
    Windows(strFileFrom).Activate
    Sheets("FM Major Inc&Dec").Select
    Range("B2:B6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D40").Select
    Windows(strFileFrom).Activate
    Range("D2:E6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B52").Select
    Windows(strFileFrom).Activate
    Range("I2:I6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D52").Select
    Windows(strFileFrom).Activate
    Range("K2:L6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Windows(strFileFrom).Activate
    Sheets("Top 20 Ben").Select
    Range("B3:B22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Sheets("Major Beneficial Holders").Select
    Range("B14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows(strFileFrom).Activate
    Range("D3:D22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Range("D14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E14").Select
    Windows(strFileFrom).Activate
    Range("F3:F22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows(strFileFrom).Activate
    Sheets("Ben Major Inc&Dec").Select
    Range("B2:B6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Range("B40").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows(strFileFrom).Activate
    Range("D2:E6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Range("D40").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B51").Select
    Windows(strFileFrom).Activate
    Range("I2:I6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D51").Select
    Windows(strFileFrom).Activate
    Range("K2:L6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
End Sub

**AB33** · 04-08-2013, 05:35 AM

This is a recorded macro. I have amended the first few lines of the code and hopefully you should be able to do the rest

Sub Macro1()

Dim strFileFrom As String
Dim strFileTo As String
 
strFileFrom = ActiveWorkbook.Name
strFileTo = "Template.xlsx"

    Windows(strFileFrom).Sheets("Top 20 Line Entries").Range("B3:B22").Copy
    Windows(strFileTo).Sheets("Top 20 Line Entries").Range("B14").PasteSpecial Paste:=xlPasteValues

    Windows(strFileFrom).Range("D3:D22").Copy
    Windows(strFileTo).Range("D14").PasteSpecial Paste:=xlPasteValues

    Windows(strFileFrom).Range("F3:F22").Copy
  
    Windows(strFileTo).Range("E14").PasteSpecial Paste:=xlPasteValues

    Windows(strFileFrom).Sheets("Top 20 FM").Range("B3:B22").Copy
    Windows(strFileTo).Sheets("Major Fund Managers").Range("B14").PasteSpecial Paste:=xlPasteValues

    Windows(strFileFrom).Activate
    Range("D3:D22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Range("D14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows(strFileFrom).Activate
    Range("F3:F22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Range("E14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B40").Select
    Windows(strFileFrom).Activate
    Sheets("FM Major Inc&Dec").Select
    Range("B2:B6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D40").Select
    Windows(strFileFrom).Activate
    Range("D2:E6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B52").Select
    Windows(strFileFrom).Activate
    Range("I2:I6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D52").Select
    Windows(strFileFrom).Activate
    Range("K2:L6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Windows(strFileFrom).Activate
    Sheets("Top 20 Ben").Select
    Range("B3:B22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Sheets("Major Beneficial Holders").Select
    Range("B14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows(strFileFrom).Activate
    Range("D3:D22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Range("D14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E14").Select
    Windows(strFileFrom).Activate
    Range("F3:F22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows(strFileFrom).Activate
    Sheets("Ben Major Inc&Dec").Select
    Range("B2:B6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Range("B40").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows(strFileFrom).Activate
    Range("D2:E6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Range("D40").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B51").Select
    Windows(strFileFrom).Activate
    Range("I2:I6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D51").Select
    Windows(strFileFrom).Activate
    Range("K2:L6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(strFileTo).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
End Sub

**PONJO** · 04-08-2013, 06:19 AM

thanx but i figured the problem its working fine with any open workbook

thanx again God Bless you

**AB33** · 04-08-2013, 06:23 AM

Ponjo,
You are welcome!

Could you please now close (Mark) this thread as solved? Go in to the top right-hand side of this page, choose "Thread Tools" from the menu, then select "solved" from the drop down menu.