Transfer/Copy Data from one workbook to another

[scottw1988]

Hello all,

I am currently writing macros that will transfer data from a template file, to what is essentially a log file. How does one go about copying data from one workbook to another. I have currently been able to transfer data from one sheet to another using the following code:

Sub TestMacro3()
'
' TestMacro2 Macro
'

'
    Range("B1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("MONTHLY REPORT").Select
    Range("B2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=YEAR(TODAY())"
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

How could I get this code to write to another workbook. I believe that I read in order to paste another file it must be opened. Is that correct? Any help would be much appreciated.

Thanks,
Scott

Moderator's note: code tags added for you

[FDibbins]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[jeffreybrown]

Hi Scott,

Instead of trying to read this code, it would help if you just laid out the particular's.

Can we start with what you are trying to accomplish?

[scottw1988]

I should also note that I need the macros to work with any workbook. The reason being that there are 12 different files that I need to pull from (one for each month). I will be pulling the info from the exact same cells each time. I really just need to know where and how to name the source WorkBook in a manner that whenever the i.e. (January, February, March etc) workbook files are being used it can still paste the correct values into the destination file which will not change.

[scottw1988]

Hi Jeff,

I am trying to write a macro that will copy/paste certain cells from one workbook to another workbook. So basically, the values entered in WB1 I want transfered to WB2.

[jeffreybrown]

There are many ways to go with this that is why we look for specifics up front.

Here is an example I worked up, but with the additional info you posted it will take some more effort (at least for me).

Option Explicit

Sub MoveData()
    Dim MyPath  As String
    Dim MyFile  As String
    Dim fNAME   As String
    Dim i       As Long
    ''''''''''''''''''''''''''''''''''''''''''''''''
    'Make sure workbook/worksheet names match
    Dim WB1     As String: WB1 = "wb1.xlsm"
    Dim WB2     As String: WB2 = "wb2.xlsm"
    Dim wsSrc   As String: wsSrc = "Sheet1"
    Dim wsDest  As String: wsDest = "Sheet1"
    ''''''''''''''''''''''''''''''''''''''''''''''''
    Dim Rng     As Range
    Dim LR      As Long

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    MyPath = ActiveWorkbook.Path & Application.PathSeparator
    MyFile = WB1
    fNAME = Dir(MyPath & MyFile)

    Workbooks.Open Filename:=MyPath & WB2

    With Workbooks(fNAME).Sheets(wsSrc)
        .Range("A1").Copy Workbooks(WB2).Sheets(wsDest).Range("A1")
    End With

    With Workbooks(WB2)
        Application.EnableEvents = False
        .Close SaveChanges:=True
        Application.EnableEvents = True
    End With
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub

[abousetta]

Hi Scott,

Maybe this example can help.

I have a workbook that is called AllYear and two other workbooks (Jan and Feb). I want to extract the exact cell from both Jan and Feb and put them in AllYear. Just put all the files on your desktop and the macro should recognize them.

Option Explicit

Sub test()

Dim MyMonths
Dim m%, r%
Dim wbJan$, wbFeb$

wbJan = CreateObject("WScript.Shell").SpecialFolders("DeskTop") & "Jan.xlsx"    ' You can replace this with an actual file path
wbFeb = CreateObject("WScript.Shell").SpecialFolders("DeskTop") & "Feb.xlsx"    ' You can replace this with an actual file path

  MyMonths = Array(wbJan, wbFeb)

    For m = LBound(MyMonths) To UBound(MyMonths)
    
        r = r + 1
        Workbooks.Open Filename:=MyMonths(m)
        ThisWorkbook.Sheets(1).Cells(r, 1) = ActiveWorkbook.Sheets(1).Cells(1, 1)
        ActiveWorkbook.Close False
    
    Next

End Sub

Let me know if things are more clear now.

abousetta