How Can You Fix PC Dll Errors Effectively?: How to Fix Error - adding a new row when another row filled in another worksheet?

adding a new row when another row filled in another worksheet

I have two excel workbooks called "forecast" and "taeh".
In these workbooks I have several sheets in each. I want to write a macro code which enables this: whenever a new row filled in
one of the worksheets (say "summary") of "taeh" workbook, I want to add a new row after the row one before of the last filled row in the worksheet (say "banks") of "forecast" workbook.
I also want to copy the formula of row one before the new added row, to
the new added row.

Could you help me on this?

Many thanks,
Good work,

Davut Elmastaş,
Turkey

Keys to the Problem adding a new row when another row filled in another worksheet

Download SmartPCFixer to Fix It (Free)

You do not have to change any of the code in the taeh.xls workbook -- but all workbooks like it that the forecast.xls workbook will try to open must have that same code in them.

Here is new code for the forecast.xls file. Replace all of what I provided for that workbook to you with this code.

When you open the workbook it will examine all of the workbooks you set up to work with in the Workbook_Open() event. It calls the other section of code for each of them. I believe now that you have had to change worksheet names and paths to workbooks
before that you will understand what you have to change in this to make it work properly for you.

Private Sub Workbook_Open()
'call the other routine for each workbook and worksheet you need to process
'before each call, set up the names of the sheets involved and the path
'to the other workbook. This will work for 1 or 1,000 workbooks.
Dim mySheetName As String
Dim otherSheetName As String
Dim otherWorkbook As String
mySheetName = "banks" ' name of the sheet in this workbook to add a row to
otherSheetName = "summary" ' if same name in all other workbooks, only need to set it 1 time
'change this to the real path to the other workbook as seen from your system
otherWorkbook = "Q:\for_DavutElmastas\taeh.xls"
ProcessNewEntriesFromOtherWorkbooks mySheetName, otherSheetName, otherWorkbook

'process another workbook (or same as before) but go to another sheet in this one
mySheetName = "banks (2)" ' change to actual sheet name in this workbook
otherSheetName = "Summary" ' sheet name to examine in the other workbook
otherWorkbook = "Q:\for_DavutElmastas\taeh2.xls" ' full path and name to other workbook
ProcessNewEntriesFromOtherWorkbooks mySheetName, otherSheetName, otherWorkbook

'repeat as needed the 4 lines of code for each process to be done
'if one of the mySheetName, OtherSheetName or otherWorkbook names
'does not change, you only need to set it one time.

'
End Sub
Private Sub ProcessNewEntriesFromOtherWorkbooks(thisWBSheetName As String, _
otherWBSheetName As String, pathToOtherWB As String)
'as this is setup now, we only anticipate having to open the taeh.xls workbook
'if there are several that need to be examined, then we may want to alter the
'way this works.
'
'change these constants as required to match the real workbook name and the sheet in it
'that must be examined.
'******
Const ourMarkerColumn = "AD" ' known column where the process marker is placed
'these refer to the sheet in this workbook to work with
Const myFirstColumn = "B"
Const myLastColumn = "AC"
'working constants and variables do not change the Const values below.
Const readyToProcess = "R" ' same as in other workbook's code
Const alreadyProcessed = "F" ' same as in other workbook's code
Dim dataWBName As String
Dim dataWSName As String ' the name of the sheet in the data workbook to examine
Dim mySheetName As String
Dim dataWB As Workbook
Dim dataWS As Worksheet
Dim markerList As Range
Dim anyMarker As Range
Dim myWS As Worksheet
Dim myLastRow As Long
Dim myNewRow As Long
Dim myCopyRange As Range

'move passed parameters into working variables
dataWBName = pathToOtherWB
dataWSName = otherWBSheetName
mySheetName = thisWBSheetName

'this prevents screen flickering and
'improves speed of this process
Application.ScreenUpdating = False
Set dataWB = Workbooks.Open(Filename:=dataWBName, UpdateLinks:=False, ReadOnly:=False)
Set dataWS = dataWB.Worksheets(dataWSName)
Set markerList = dataWS.Range(ourMarkerColumn & "1:" & _
   dataWS.Range(ourMarkerColumn & Rows.Count).End(xlUp).Address)
For Each anyMarker In markerList
    If anyMarker = readyToProcess Then
      'this is a full row that has not yet been accounted for in this workbook.
      Set myWS = ThisWorkbook.Worksheets(mySheetName) ' the banks sheet
      myLastRow = myWS.Range("B" & Rows.Count).End(xlUp).Row
      myWS.Range("A" & myLastRow).EntireRow.Insert ' add new blank row
      myNewRow = myLastRow
      myLastRow = myLastRow + 1
      Set myCopyRange = myWS.Range(myFirstColumn & myNewRow - 1 & ":" _
       & myLastColumn & myNewRow)
      myCopyRange.FillDown ' should copy the formulas if they are formed properly.
      'remember to mark the row as processed in the taeh.xls file
      Application.EnableEvents = False ' don't trigger Sheet_Change() in taeh.xls
      anyMarker = alreadyProcessed
      Application.EnableEvents = True ' re-enable event processing.
      'add totals for columns E, F, G, H and I
      myWS.Range("E" & myLastRow & ":I" & myLastRow).FormulaR1C1 = _
       "=SUM(R2C:R[-1]C)"
    End If
Next
'good housekeeping practices
Set myCopyRange = Nothing
Set markerList = Nothing
Set dataWS = Nothing
'close the other workbook
Application.DisplayAlerts = False
dataWB.Close True
Application.DisplayAlerts = True
Set dataWB = Nothing
End Sub

Make Sure that your Computer Meets the System Requirements

You need to double-check your computer's hardware configuration against the following Microsoft requirements. If your computer hardware is not up to par, then make sure you upgrade where necessary before continuing:

Microsoft Windows Requirements :

1 gigahertz (GHz) or faster 32-bit (x86) or 64-bit (x64) processor
1 gigabyte (GB) RAM (32-bit) or 2 GB RAM (64-bit)
16 GB available hard disk space (32-bit) or 20 GB (64-bit)

If your computer meets the minimum requirements above, then continue on…

Recommended Method to Repair the Problem: adding a new row when another row filled in another worksheet:

How to Fix adding a new row when another row filled in another worksheet with SmartPCFixer?

1. Download SmartPCFixer . Install it on your computer. Click Scan, and it will perform a scan for your computer. The junk files will be shown in the scan result.

2. After the scan is finished, you can see the errors and problems which need to be fixed.

3. The Repair part is finished, the speed of your computer will be much higher than before and the errors have been fixed. You can also use other functions in SmartPCFixer. Like dll downloading, windows updating and print spooler error repair.

How Can You Fix PC Dll Errors Effectively?

2016年3月31日星期四

How to Fix Error - adding a new row when another row filled in another worksheet?

adding a new row when another row filled in another worksheet

Keys to the Problem adding a new row when another row filled in another worksheet

Make Sure that your Computer Meets the System Requirements

Recommended Method to Repair the Problem: adding a new row when another row filled in another worksheet:

How to Fix adding a new row when another row filled in another worksheet with SmartPCFixer?

没有评论:

发表评论