Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ron de bruins code won't concatenate
Hi all - got this code from Ron's site. but it only copies the last worksheet
that begins with 1111; there are 3 worksheets that begin with 1111. HELP! If each 1111 worksheet has 100 rows, I am expecting the new worksheet to have 300 rows. Thanks T Sub CopyRangeFromMultiWorksheets() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim CopyRng As Range With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "RDBMergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("RDBMergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "RDBMergeSheet" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "RDBMergeSheet" 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets If LCase(Left(sh.Name, 4)) = "1111" Then Set CopyRng = sh.Range("A1").CurrentRegion 'Fill in the range that you want to copy Set CopyRng = sh.Range("A1").CurrentRegion 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count DestSh.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 DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If Next ExitTheSub: Application.Goto DestSh.Cells(1) 'AutoFit the column width in the DestSh sheet DestSh.Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ron de bruins code won't concatenate
This line
If LCase(Left(sh.Name, 4)) = "1111" Then looks at the first 4 characters of the sheet name. So either the wrong workbook is active (and it only has a single worksheet that begins with 1111) or your worksheet names have something else before those 1111's--maybe an extra space? Theo wrote: Hi all - got this code from Ron's site. but it only copies the last worksheet that begins with 1111; there are 3 worksheets that begin with 1111. HELP! If each 1111 worksheet has 100 rows, I am expecting the new worksheet to have 300 rows. Thanks T Sub CopyRangeFromMultiWorksheets() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim CopyRng As Range With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "RDBMergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("RDBMergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "RDBMergeSheet" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "RDBMergeSheet" 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets If LCase(Left(sh.Name, 4)) = "1111" Then Set CopyRng = sh.Range("A1").CurrentRegion 'Fill in the range that you want to copy Set CopyRng = sh.Range("A1").CurrentRegion 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count DestSh.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 DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If Next ExitTheSub: Application.Goto DestSh.Cells(1) 'AutoFit the column width in the DestSh sheet DestSh.Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ron de bruins code won't concatenate
Nope -neither of those are the case.
I've tried other things, according to some of his tips, like naming the worksheets in an array, but I still only get one worksheet - the last one. T "Dave Peterson" wrote: This line If LCase(Left(sh.Name, 4)) = "1111" Then looks at the first 4 characters of the sheet name. So either the wrong workbook is active (and it only has a single worksheet that begins with 1111) or your worksheet names have something else before those 1111's--maybe an extra space? Theo wrote: Hi all - got this code from Ron's site. but it only copies the last worksheet that begins with 1111; there are 3 worksheets that begin with 1111. HELP! If each 1111 worksheet has 100 rows, I am expecting the new worksheet to have 300 rows. Thanks T Sub CopyRangeFromMultiWorksheets() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim CopyRng As Range With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "RDBMergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("RDBMergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "RDBMergeSheet" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "RDBMergeSheet" 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets If LCase(Left(sh.Name, 4)) = "1111" Then Set CopyRng = sh.Range("A1").CurrentRegion 'Fill in the range that you want to copy Set CopyRng = sh.Range("A1").CurrentRegion 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count DestSh.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 DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If Next ExitTheSub: Application.Goto DestSh.Cells(1) 'AutoFit the column width in the DestSh sheet DestSh.Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ron de bruins code won't concatenate
I don't see where you ever define or redefine your variable LAST.
HTH, Barb Reinhardt "Theo" wrote: Hi all - got this code from Ron's site. but it only copies the last worksheet that begins with 1111; there are 3 worksheets that begin with 1111. HELP! If each 1111 worksheet has 100 rows, I am expecting the new worksheet to have 300 rows. Thanks T Sub CopyRangeFromMultiWorksheets() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim CopyRng As Range With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "RDBMergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("RDBMergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "RDBMergeSheet" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "RDBMergeSheet" 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets If LCase(Left(sh.Name, 4)) = "1111" Then Set CopyRng = sh.Range("A1").CurrentRegion 'Fill in the range that you want to copy Set CopyRng = sh.Range("A1").CurrentRegion 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count DestSh.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 DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If Next ExitTheSub: Application.Goto DestSh.Cells(1) 'AutoFit the column width in the DestSh sheet DestSh.Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ron de bruins code won't concatenate
It's copied straight from Ron's Excel Tips, so if it's missing, I'm not sure
where/how to add it. This is the last bit of code: 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 "Barb Reinhardt" wrote: I don't see where you ever define or redefine your variable LAST. HTH, Barb Reinhardt "Theo" wrote: Hi all - got this code from Ron's site. but it only copies the last worksheet that begins with 1111; there are 3 worksheets that begin with 1111. HELP! If each 1111 worksheet has 100 rows, I am expecting the new worksheet to have 300 rows. Thanks T Sub CopyRangeFromMultiWorksheets() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim CopyRng As Range With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "RDBMergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("RDBMergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "RDBMergeSheet" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "RDBMergeSheet" 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets If LCase(Left(sh.Name, 4)) = "1111" Then Set CopyRng = sh.Range("A1").CurrentRegion 'Fill in the range that you want to copy Set CopyRng = sh.Range("A1").CurrentRegion 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count DestSh.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 DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If Next ExitTheSub: Application.Goto DestSh.Cells(1) 'AutoFit the column width in the DestSh sheet DestSh.Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ron de bruins code won't concatenate
After these lines put this
'Fill in the range that you want to copy Set CopyRng = sh.Range("A1").CurrentRegion last = lastrow(sh) This is untested, but your variable Last always is Zero since you don't set it when you copy data. I bet you're copying each sheet, but one sheet overwrites the next. WHy not put a break point in and see what's happening. HTH, Barb Reinhardt "Theo" wrote It's copied straight from Ron's Excel Tips, so if it's missing, I'm not sure where/how to add it. This is the last bit of code: 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 "Barb Reinhardt" wrote: I don't see where you ever define or redefine your variable LAST. HTH, Barb Reinhardt "Theo" wrote: Hi all - got this code from Ron's site. but it only copies the last worksheet that begins with 1111; there are 3 worksheets that begin with 1111. HELP! If each 1111 worksheet has 100 rows, I am expecting the new worksheet to have 300 rows. Thanks T Sub CopyRangeFromMultiWorksheets() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim CopyRng As Range With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "RDBMergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("RDBMergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "RDBMergeSheet" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "RDBMergeSheet" 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets If LCase(Left(sh.Name, 4)) = "1111" Then Set CopyRng = sh.Range("A1").CurrentRegion 'Fill in the range that you want to copy Set CopyRng = sh.Range("A1").CurrentRegion 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count DestSh.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 DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If Next ExitTheSub: Application.Goto DestSh.Cells(1) 'AutoFit the column width in the DestSh sheet DestSh.Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ron de bruins code won't concatenate
Is it only processing one worksheet or is it only copying once?
I'd change this: For Each sh In ActiveWorkbook.Worksheets If LCase(Left(sh.Name, 4)) = "1111" Then to: For Each sh In ActiveWorkbook.Worksheets msgbox sh.range("A1").address(external:=true) If LCase(Left(sh.Name, 4)) = "1111" Then msgbox "processing " & sh.name The first msgbox will show you the entire address for A1 of every sheet (including drive, path, filename, sheet name and address). Maybe you'll see something wrong. The second msgbox will tell you if the loop is at least starting. And if it is, then I bet Barb has the correct solution. Theo wrote: Nope -neither of those are the case. I've tried other things, according to some of his tips, like naming the worksheets in an array, but I still only get one worksheet - the last one. T "Dave Peterson" wrote: This line If LCase(Left(sh.Name, 4)) = "1111" Then looks at the first 4 characters of the sheet name. So either the wrong workbook is active (and it only has a single worksheet that begins with 1111) or your worksheet names have something else before those 1111's--maybe an extra space? Theo wrote: Hi all - got this code from Ron's site. but it only copies the last worksheet that begins with 1111; there are 3 worksheets that begin with 1111. HELP! If each 1111 worksheet has 100 rows, I am expecting the new worksheet to have 300 rows. Thanks T Sub CopyRangeFromMultiWorksheets() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim CopyRng As Range With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "RDBMergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("RDBMergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "RDBMergeSheet" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "RDBMergeSheet" 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets If LCase(Left(sh.Name, 4)) = "1111" Then Set CopyRng = sh.Range("A1").CurrentRegion 'Fill in the range that you want to copy Set CopyRng = sh.Range("A1").CurrentRegion 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count DestSh.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 DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If Next ExitTheSub: Application.Goto DestSh.Cells(1) 'AutoFit the column width in the DestSh sheet DestSh.Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With 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 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ron de bruins code won't concatenate
Thanks Barb and Dave - I made those changes, and I'm worse off than I was
before. Now the new sheet is created, but nothing is copied. Dave I am getting the first msg box, but not the second box. At this point, I've made so many changes, I'm scrapping it and starting over. I think I'll go back to Ron's site and try again. I must have missed something. Or, maybe there is some correction to his code that is captured in the discussion group comments, but not on his site. Thanks all. T "Barb Reinhardt" wrote: After these lines put this 'Fill in the range that you want to copy Set CopyRng = sh.Range("A1").CurrentRegion last = lastrow(sh) This is untested, but your variable Last always is Zero since you don't set it when you copy data. I bet you're copying each sheet, but one sheet overwrites the next. WHy not put a break point in and see what's happening. HTH, Barb Reinhardt "Theo" wrote It's copied straight from Ron's Excel Tips, so if it's missing, I'm not sure where/how to add it. This is the last bit of code: 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 "Barb Reinhardt" wrote: I don't see where you ever define or redefine your variable LAST. HTH, Barb Reinhardt "Theo" wrote: Hi all - got this code from Ron's site. but it only copies the last worksheet that begins with 1111; there are 3 worksheets that begin with 1111. HELP! If each 1111 worksheet has 100 rows, I am expecting the new worksheet to have 300 rows. Thanks T Sub CopyRangeFromMultiWorksheets() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim CopyRng As Range With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "RDBMergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("RDBMergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "RDBMergeSheet" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "RDBMergeSheet" 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets If LCase(Left(sh.Name, 4)) = "1111" Then Set CopyRng = sh.Range("A1").CurrentRegion 'Fill in the range that you want to copy Set CopyRng = sh.Range("A1").CurrentRegion 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count DestSh.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 DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If Next ExitTheSub: Application.Goto DestSh.Cells(1) 'AutoFit the column width in the DestSh sheet DestSh.Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
ron de bruins code won't concatenate
Hi Theo
Download the example workbook first to test the code http://www.rondebruin.nl/copy2.htm After that change the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Theo" wrote in message ... Thanks Barb and Dave - I made those changes, and I'm worse off than I was before. Now the new sheet is created, but nothing is copied. Dave I am getting the first msg box, but not the second box. At this point, I've made so many changes, I'm scrapping it and starting over. I think I'll go back to Ron's site and try again. I must have missed something. Or, maybe there is some correction to his code that is captured in the discussion group comments, but not on his site. Thanks all. T "Barb Reinhardt" wrote: After these lines put this 'Fill in the range that you want to copy Set CopyRng = sh.Range("A1").CurrentRegion last = lastrow(sh) This is untested, but your variable Last always is Zero since you don't set it when you copy data. I bet you're copying each sheet, but one sheet overwrites the next. WHy not put a break point in and see what's happening. HTH, Barb Reinhardt "Theo" wrote It's copied straight from Ron's Excel Tips, so if it's missing, I'm not sure where/how to add it. This is the last bit of code: 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 "Barb Reinhardt" wrote: I don't see where you ever define or redefine your variable LAST. HTH, Barb Reinhardt "Theo" wrote: Hi all - got this code from Ron's site. but it only copies the last worksheet that begins with 1111; there are 3 worksheets that begin with 1111. HELP! If each 1111 worksheet has 100 rows, I am expecting the new worksheet to have 300 rows. Thanks T Sub CopyRangeFromMultiWorksheets() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim CopyRng As Range With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "RDBMergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("RDBMergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "RDBMergeSheet" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "RDBMergeSheet" 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets If LCase(Left(sh.Name, 4)) = "1111" Then Set CopyRng = sh.Range("A1").CurrentRegion 'Fill in the range that you want to copy Set CopyRng = sh.Range("A1").CurrentRegion 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count DestSh.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 DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If Next ExitTheSub: Application.Goto DestSh.Cells(1) 'AutoFit the column width in the DestSh sheet DestSh.Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
ron de bruins code won't concatenate
Hi Ron!
I just started from scratch, and recopied your code. I ran it as is, and then I added my changes one at a time, and then tested each change to make sure I didn't mess it up. It works perfectly. Thanks goodness for your Excel tips - I use them all the time! I'll be more careful next time before I post! Theo "Ron de Bruin" wrote: Hi Theo Download the example workbook first to test the code http://www.rondebruin.nl/copy2.htm After that change the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Theo" wrote in message ... Thanks Barb and Dave - I made those changes, and I'm worse off than I was before. Now the new sheet is created, but nothing is copied. Dave I am getting the first msg box, but not the second box. At this point, I've made so many changes, I'm scrapping it and starting over. I think I'll go back to Ron's site and try again. I must have missed something. Or, maybe there is some correction to his code that is captured in the discussion group comments, but not on his site. Thanks all. T "Barb Reinhardt" wrote: After these lines put this 'Fill in the range that you want to copy Set CopyRng = sh.Range("A1").CurrentRegion last = lastrow(sh) This is untested, but your variable Last always is Zero since you don't set it when you copy data. I bet you're copying each sheet, but one sheet overwrites the next. WHy not put a break point in and see what's happening. HTH, Barb Reinhardt "Theo" wrote It's copied straight from Ron's Excel Tips, so if it's missing, I'm not sure where/how to add it. This is the last bit of code: 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 "Barb Reinhardt" wrote: I don't see where you ever define or redefine your variable LAST. HTH, Barb Reinhardt "Theo" wrote: Hi all - got this code from Ron's site. but it only copies the last worksheet that begins with 1111; there are 3 worksheets that begin with 1111. HELP! If each 1111 worksheet has 100 rows, I am expecting the new worksheet to have 300 rows. Thanks T Sub CopyRangeFromMultiWorksheets() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim CopyRng As Range With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "RDBMergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("RDBMergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "RDBMergeSheet" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "RDBMergeSheet" 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets If LCase(Left(sh.Name, 4)) = "1111" Then Set CopyRng = sh.Range("A1").CurrentRegion 'Fill in the range that you want to copy Set CopyRng = sh.Range("A1").CurrentRegion 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count DestSh.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 DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If Next ExitTheSub: Application.Goto DestSh.Cells(1) 'AutoFit the column width in the DestSh sheet DestSh.Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help getting code from ron de bruins site | Excel Programming | |||
VB code to concatenate 100+ cells | Excel Programming | |||
post code & suburb concatenate | Excel Discussion (Misc queries) | |||
I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel | |||
Ron de Bruins Delete Row Code Q | Excel Programming |