Moving Data Between Sheets

[bsteeves]

Hi everyone,

I have one very large piece of data located in one worksheet that I use across ten other worksheets. I simply copy and paste this data into each worksheet each morning after the data has been refreshed. Is there any way to be able to simply have my master sheet update and then get coppied to all of my other sheets? Thanks in advance.

 

[texasalynn]

do a macro record, then if unsure how to modify the code post it back here and someone will help you sort it out.

 

[jfklbj]

Hi everyone,

I have one very large piece of data located in one worksheet that I use across ten other worksheets. I simply copy and paste this data into each worksheet each morning after the data has been refreshed. Is there any way to be able to simply have my master sheet update and then get coppied to all of my other sheets? Thanks in advance.

Can't you just use cell references to the large piece of data in the worksheets that you want updated? e.g. =Sheet1!A14

 

[bsteeves]

I am using this snippet of code in another sheet that I have, but how do I ammend it so it will copy to another workbook?


Sheets("Holdings").Range("A4:N" & LR).Copy Destination:=Sheets("Template").Range("A18")

 

[jfklbj]

Sheets("Holdings").Range("A4:N" & LR).Copy
Set wkb = Nothing
On Error Resume Next
Set wkb = Application.Workbooks _
("C:\\test.xls")
On Error GoTo ErrorHandler
If wkb Is Nothing Then
Set wkb = Application.Workbooks.Open( _
"C:\\test.xls" _
, UpdateLinks:=0)
End If

Sheets("Template").Range("A18").Paste

 

[Datsmart]

This code will copy the coded range to three worksheets in the same workbook:

Sub Copy2SheetList()
    LR = Sheets("Holdings").Range("A65536").End(xlUp).Row
    For Each wks In ThisWorkbook.Worksheets(Array("Template", "Sheet1", "Sheet2"))
        With wks
            ws = wks.Name
            Sheets("Holdings").Range("A4:N" & LR).Copy Sheets(ws).Range("A18")
        End With
    Next wks
End Sub

You can add additional worksheets to the list I have in the code.
I used "Template, Sheet1, Sheet2".

 

[bsteeves]

I need the code to paste to different workbooks though:

Sheets("Holdings").Range("A4:N" & LR).Copy
Set wkb = Nothing
On Error Resume Next
Set wkb = Application.Workbooks _
("C:\\test.xls")
On Error GoTo ErrorHandler
If wkb Is Nothing Then
Set wkb = Application.Workbooks.Open( _
"C:\\test.xls" _
, UpdateLinks:=0)
End If
Sheets("Template").Range("A18").Paste
End Sub

As for this code, I am getting an error at the line "On Error GoTo ErrorHandler". The error is "Line not defined"

 

[jfklbj]

Sorry, I assumed you had an errorhandler in your application. Datsmarts' code should work well as long as all the workbooks in the array exist. If one gets moved or deleted you will get an error message.

 

[bsteeves]

I am getting a subscript out of range error with this code:

Sub Copy_Sheets()
Application.ScreenUpdating = False

With Workbooks("Book2.xlsm")

Application.DisplayAlerts = False

Application.DisplayAlerts = True
Workbooks("Test.xlsx").Sheets("Sheet1").Range("A1").Copy Destination:=Workbooks("Book2.xlsx").Sheets("Bonds").Range("A1")

End With

Application.ScreenUpdating = True
End Sub

 

[texasalynn]

you need to make a few changes like this

Dim wb As Variant, wb2 As Variant
wb = "COA.xlsx"
wb2 = "Invoice.xls"
Workbooks(wb).Sheets("Sheet1").Range("A1").Copy Destination:=Workbooks(wb2).Sheets("Bonds").Range("A1")