Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicating Changes from Column A and B of Master Workbook to ColumnA and B of Other Workbooks - - Please help me, I can save days if my problem
Hi Everyone,
I have this report that takes 3 days to finish. I have 3 workbooks with several sheets and a master file with a single sheet named ZCA which I used for data update only. All sheets are identical. My concern is on Column A and B of each worksheet. Column A is where the account code is placed and Column B is the equivalent account name. New account code/account name is added every month, as a result i need to take effect the changes manually to all other sheets too. The row reference cell should be identical to all sheets also. I used only the Zca to check changes then afterwards I implement changes to all other workbooks/sheets manually. Is there a way for me to take effect changes in Col A and B from ZCA sheet to all other sheets from other workbooks? Many many thanks in advance! Jerome |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicating Changes from Column A and B of Master Workbook to Colu
Here is how you could do it:
There are a couple things this assumes. Only "master" will have rows that may be inserted. If that is not the case, then this will mess you up big time! Sub MakeSame() Dim DataArray(50000, 4) As Variant Dim Fnd As Double 'pick the right line: Windows("master.xls").Activate 'or Sheets("master").Select x = 1 Do While True If Cells(x, 1).Value = Empty Then Exit Do Fnd = Fnd + 1 DataArray(Fnd, 1) = Cells(x, 1).Value DataArray(Fnd, 2) = Cells(x, 2).Value DataArray(Fnd, 3) = x x = x + 1 Loop For Y = 1 To 2 If Y = 1 Then 'pick the right line below: Windows("sheet2.xls").Activate 'or Sheets("sheet2").Select ElseIf Y = 2 Then 'pick the right line below: Windows("sheet3.xls").Activate 'or Sheets("sheet3").Select End If For x = 1 To Fnd If Cells(x, 1).Value < DataArray(x, 1) Then 'insert a row and make it match Rows(x & ":" & x).Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Cells(x, 1).Value = DataArray(x, 1) Cells(x, 2).Value = DataArray(x, 2) End If Next Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating a master workbook with 80 workbooks | Excel Discussion (Misc queries) | |||
Duplicating Changes from Column A and B of Master Workbook to ColumnA and B of Other Workbooks - - Please help me, I can save days if my problem | Excel Programming | |||
Search a column, enable input fields, save to row & columna, reset | Excel Programming | |||
Combine workbooks into one master workbook. | Excel Programming | |||
Importing Workbooks into a Master Workbook | Excel Worksheet Functions |