Counting/Adding rows

C

claytorm

Hi

I have a large workbook, with one main sheet with all the calculations
and over 50 other sheets containing data. All the data sheets have
columns, but different numbers of rows. I wish to bring all the dat
from the data sheets into the main sheet, still in 4 columns.

I have experimented with COUNTA, and this might be part of the answer
Is a macro needed?

A separate question: I have some knowledge of Excel, but none of VB an
macros. What is the best starting point?

Thanks,

Bertie
 
C

claytorm

Frank, thanks for the link. I have copied the code, and can no
successfully merge data from the data sheets into the main sheet. I no
want to make a small change to the code, but don't know how to do it.

I want the contents of cell A1 in each of the data sheets to be place
in column A of the main sheet for each row of data that gets imported
Any ideas?

The code so far is:


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 Test5()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim shLast As Long
Dim Last As Long

On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("Master").Name) = 0 Then
On Error GoTo 0
Application.ScreenUpdating = False
Set DestSh = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
Last = LastRow(DestSh)
shLast = LastRow(sh)

sh.Range(sh.Rows(3), sh.Rows(shLast)).Cop
DestSh.Cells(Last + 1, "A")
'Instead of this line you can use the code below t
copy only the values
'or use the PasteSpecial option to paste the forma
also.


'With sh.Range(sh.Rows(3), sh.Rows(shLast))
'DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
'.Columns.Count).Value = .Value
'End With


'sh.Range(sh.Rows(3), sh.Rows(shLast)).Copy
'With DestSh.Cells(Last + 1, "A")
' .PasteSpecial xlPasteValues, , False, False
' .PasteSpecial xlPasteFormats, , False, False
' Application.CutCopyMode = False
'End With

End If
Next
Cells(1).Select
Application.ScreenUpdating = True
Else
MsgBox "The sheet Master already exist"
End If
End Sub

---
Also, can anyone recommend a good VB book to get started?

Thanks very much

Bertie
 
Top