Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I cannot find what is wrong with this code please Help me
Hi everybody,and thanks in advance
I have the code as follow. What is supposed to do is to delete all the information in tab BackLog_Summary from cell A4 to the end and then copy from certain tabs cells A4 to AZ6 into BackLog_Summary starting in cell A4 the first time and then continue to the last empty row in column A. What happens is that sometimes it works and others delete change headers in row 3, after that if I push the button again it fix it, then again if you push the button it screw up everythin. Sub Backlog() ' ' Backlog Macro ' Dim sh As Worksheet 'Dim DestSh As Worksheet Dim Last As Long Dim CopyRng As Range Dim DeleteRng As String Dim lastRow1 As Long 'With Application ' .ScreenUpdating = False ' .EnableEvents = False 'End With 'Delete information in sheet "BackLog_Summary" if it exist Sheets("BackLog_Summary").Select lastRow1 = Range("A" & Rows.Count).End(xlUp).Row DeleteRng = Range("A4:BA" & lastRow1).Select On Error Resume Next Selection.Delete 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets If LCase(Left(sh.Name, 2)) = "es" Or LCase(Left(sh.Name, 2)) = "cs" Then 'Find the last row with data on the DestSh Last = lastRow(Sheets("BackLog_Summary")) 'Fill in the range that you want to copy Set CopyRng = sh.Range("A4:AZ5") 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count Sheets("BackLog_Summary").Rows.Count Then MsgBox "There are not enough rows in the Destsh" GoTo ExitTheSub End If 'This example copies values/formats, if you only want to copy the 'values or want to copy everything look at the example below this macro CopyRng.Copy With Sheets("BackLog_Summary").Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With 'Optional: This will copy the sheet name in the BA column Sheets("BackLog_Summary").Cells(Last + 1, "BA").Resize(CopyRng.Rows.Count).Value = sh.Name End If Next ExitTheSub: Application.Goto Sheets("BackLog_Summary").Cells(1) 'AutoFit the column width in the DestSh sheet Sheets("BackLog_Summary").Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With ' Select Menu Tab Worksheets("Menu").Select End Sub Function lastRow(sh As Worksheet) On Error Resume Next lastRow = sh.Cells.Find(What:="*", _ after:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function LastCol(sh As Worksheet) On Error Resume Next LastCol = sh.Cells.Find(What:="*", _ after:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find number - where did I go wrong? | Excel Programming | |||
FIND method doesn't work in UDF. What's wrong with this code? | Excel Programming | |||
What is wrong with this code? | Excel Programming | |||
What's wrong w/my code? | Excel Programming | |||
Can anyone find what is wrong with the following? | Excel Programming |