copying data from Excel spreadsheet to another

[DavidB]

I need to copy data from 1 excel speadsheet to another.

I have tried this using a Excel Macro but my problem is....
Can't get it to Flip sheets!
(ie. copy from sheet 1 to sheet 1 in master is
OK but how do I go to next tab and repeat??)

Daily spread sheet had 80 tabs each containing data for one of the machines.

(this is not constant as some machines are off line or faulty)

Col1, Col2,Col3, Col9 ... ie..4-8 are off.

The master sheet contains all Cols 1 to 100.

Data is read from each sheet on-mass ie. Col1 is copied to Col1 in master -
there are other columns in the master which calculates results.

 

[Dave Peterson]

So you have a workbook with lots of worksheets.

One worksheet is named "master". The other worksheets are named Col1, col2,
col3, ..., col100.

And not all these worksheets will exist.

Does the data in each of the Col* worksheets appear in Column A?

Does Col1 data always go into a specific column in the master worksheet (say
Column 5/column E)?

If yes:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim mstrWks As Worksheet
Dim FirstCol As Long
Dim wksName As String

Set mstrWks = Worksheets("master")

FirstCol = 5 'COL1 data goes to column 5 (aka E:E)

For Each wks In ActiveWorkbook.Worksheets
If LCase(wks.Name) Like "col*" Then
wksName = Mid(wks.Name, 4) 'drop the COL in COL###
If IsNumeric(wksName) Then
'if it's a number then copy the data
wks.Range("a:a").Copy _
Destination:=mstrWks.Cells(1, CLng(wksName) + FirstCol - 1)
End If
End If
Next wks

End Sub