Quote:
Originally Posted by ryguy551
i am trying to make a macro that will allow me to merge spreadsheets with specific names....one of the sheet's name 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....This code works fine but i want to add another if statement to detect other names of sheets thanks!
|
Well, rather that try to work out the meaning of the whole module let me see whether I can focus on just the part you need help with:
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
This, you say, works fine, but when you add another If statement you get errors. But in that case, we don't need to see the code that works fine; we need to see the code that doesn't work, and hear what errors it generates.
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?
05-22-2014, 03:58 PM
Similar Threads
Problem with macro
Windows 7 64bit 
Office 2010 32bit
Threaded Mode