#1
|
|||
|
|||
EXCEL macro problem please help!
Hello i am trying to make a macro that will allow me to merge specific spreadsheets with specific names. As you will see in my code one of the sheets names is planting a. when i run the macro with just planting a it works perfectly but when i try to add another if statement to get multiple sheets to merge i get errors here is the code please help with what i can add.
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 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 summary sheet if it exists. Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("RDBMergeSheet").Delete On Error Goto 0 Application.DisplayAlerts = True ' Add a new summary worksheet. Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "RDBMergeSheet" ' Loop through all worksheets and copy the data to the ' summary worksheet. For Each sh In ActiveWorkbook.Worksheets If LCase(Left(sh.Name, 10)) = "planting a" Then ' Find the last row with data on the summary worksheet. Last = LastRow(DestSh) ' Specify the range to place the data. Set CopyRng = sh.Rows("1:500") ' Test to see whether there are enough rows in the summary ' worksheet to copy all the data. If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then MsgBox "There are not enough rows in the " & _ "summary worksheet to place the data." Goto ExitTheSub End If ' This statement copies values and formats from each ' worksheet. CopyRng.Copy With DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With ' Optional: This statement will copy the sheet ' name in the H column. End If Next ExitTheSub: Application.Goto DestSh.Cells(1) ' AutoFit the column width in the summary sheet. DestSh.Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Last edited by macropod; 05-22-2014 at 03:33 PM. Reason: Added code tags & formatting |
#2
|
||||
|
||||
The simplest way, assuming each of the extra sheets is to be processed the same way as your existing one, it to add however many OR conditions you need to this line:
If LCase(Left(sh.Name, 10)) = "planting a" Then For example: If LCase(Left(sh.Name, 10)) = "planting a" Or LCase(Left(sh.Name, 10)) = "planting b" Then PS: When posting code, please use the code tags. They're on the 'Go Advanced' tab below the Reply box.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
||||
|
||||
Quote:
Code:
For Each sh In ActiveWorkbook.Worksheets If LCase(Left(sh.Name, 10)) = "planting a" Then Last = LastRow(DestSh) Set CopyRng = sh.Rows("1:500") 'specify the range to place the data ' Test to see whether there are enough rows in the summary ' worksheet to copy all the data. If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then MsgBox "There are not enough rows in the summary worksheet to place the data." Goto ExitTheSub End If ' Copy values and formats from each worksheet. CopyRng.Copy With DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If Next But maybe I'm onto something already: Why do you need another If statement? Why do you need even the first one? Don't you want to copy the data from all the other worksheets to the new summary? Why do you care what the source worksheet name is? |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Problem with macro | MS baby | Excel Programming | 4 | 03-31-2014 02:47 PM |
Problem with macro | tmill29 | Excel Programming | 1 | 06-08-2013 09:59 AM |
Moving data macro problem | MattMurdock | Excel Programming | 1 | 07-20-2012 04:49 AM |
Another simple macro problem | Ulodesk | Word VBA | 1 | 06-08-2012 06:24 PM |