CODEWITHSUNDEEP
excelvba
malasoup
malasoup

Reputation: 3

How to paste value into another workbook?

I'm working on a function that will be used for quarterly reporting for projects.

I have to update a template (including changing the quarterly dates) but there are over 100 projects (hence files), so it would be troublesome to change them one by one.

I am trying to create a macro that when you enter the date in e.g. cell A1 of the master template, all of the project template dates will update, and the files will autosave.

All of these files are in the same folder. The files open up when I change cell A1, they save all at the same time, but nothing changes in the folders, except in the master template.

I've tried many methods, they either return errors or continue to be blank.

Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.xls*")
        Do While xFileName <> ""
           With Workbooks.Open(xFdItem & xFileName)
               'Assigns the value to cellValue
                Workbooks("Book2.xlsm").Worksheets("Sheet1").Range("A1").Copy
                ActiveWorkbook.Worksheets("Sheet1").Range("A2").PasteSpecial Paste:=xlPasteFormats
                ActiveWorkbook.Save
            End With
            xFileName = Dir
        Loop
    End If
End If
End Sub

Upvotes: 0

Views: 42

Answers (3)

Error 1004
Error 1004

Reputation: 8220

This is a general example how to copy/paste from one range to another. It s better to avoid ActiveWorkbook.

Sub Copy_Paste()
    
    With ThisWorkbook
    
        .Worksheets("Sheet1").Range("A1:A5").Copy
    
        .Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
    
        Application.CutCopyMode = False
        
    End With
  
End Sub

Upvotes: 0

jamheadart
jamheadart

Reputation: 5313

Just FYI you don't need to use Copy, you can set values directly, so

    With Workbooks.Open(xFdItem & xFileName)
        'Assigns the value to cellValue
         Workbooks("Book2.xlsm").Worksheets("Sheet1").Range("A1").Copy
         ActiveWorkbook.Worksheets("Sheet1").Range("A2").PasteSpecial Paste:=xlPasteFormats
         ActiveWorkbook.Save
    End With

Would become:

    With Workbooks.Open(xFdItem & xFileName)
        'Assigns the value to cellValue
         ActiveWorkbook.Worksheets("Sheet1").Range("A2").Value = _
         Workbooks("Book2.xlsm").Worksheets("Sheet1").Range("A1").Value
         ActiveWorkbook.Save
    End With

It's a bit quicker and can prevent errors if users are playing around with the computer while macros are running!

Upvotes: 1

FaneDuru
FaneDuru

Reputation: 42236

Paste:=xlPasteFormats pastes only the format. So, the cell will be blank...

Try xlPasteValues instead.

Upvotes: 2

Related Questions