Moving Data Between Sheets

bsteeves

Active Member
Joined
Jul 17, 2008
Messages
393
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.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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

Board Regular
Joined
Feb 9, 2009
Messages
68
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

Active Member
Joined
Jul 17, 2008
Messages
393
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

Board Regular
Joined
Feb 9, 2009
Messages
68

ADVERTISEMENT

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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
This code will copy the coded range to three worksheets in the same workbook:
Code:
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

Active Member
Joined
Jul 17, 2008
Messages
393

ADVERTISEMENT

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

Board Regular
Joined
Feb 9, 2009
Messages
68
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

Active Member
Joined
Jul 17, 2008
Messages
393
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

Well-known Member
Joined
May 19, 2002
Messages
8,458
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,123,110
Messages
5,599,773
Members
414,336
Latest member
Nicolas2465

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top