Can this be done

[harshab6]

I have got 78 worsheet in a single workbook out of that 39 sheets are named as june 11, june 11(1),june 11(2)…….june11(39) and other 39 is named as cash for june11, cash for june 11(2),…….Cash for june11(39). All the data from June11 to june(39) should be consolidated in single sheet(excluding blanks) and data from cash for june 11 to cash for june(39) in other sheet(excluding blanks) .I know the macro for consolidating all the sheets in single sheet .But I am unable to figure out how to write coding in this Criteria I am trying it from 2 days but didn,t get any result can anyone help me with this.

 

[machopicho]

This is an example to adapt at your requirements:

Sub mm()
For i = 1 To Sheets.Count
If Mid(Sheets(i).Name, 1, 6) = "June11" Then
r = 1
While Sheets("Total").Cells(r, 1) <> 0
r = r + 1
Wend
Sheets("Total").Cells(r, 1) = Sheets(i).Cells(1, 1)
End If
Next
End Sub

Something like this?

 

[harshab6]

Hi Machopicho,

thanks for the reply but this doesnot fullfill my creteria.I have written certain code but its shows the error message cannot figure were I went wrong can you put some light on it.

Sub mergesheets()

Dim ws As Worksheet, wsOut As Worksheet, sName As String
sName = "june'11"
Set wsOut = Worksheets.Add(after:=Sheets(Sheets.Count))
For Each ws In Worksheets
If UCase(ws.Name) Like UCase(sName) & "*" Then
ws.UsedRange.Offset(3).Copy
With Range("A65536").End(xlUp).Offset(1, 0)
PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

End Sub
thanks for help
Harsha

 

[machopicho]

...i don't know.
Sorry!