Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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. |
#2
![]() |
|||
|
|||
![]()
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 DavidB wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Fetching External Data from Excel | Excel Discussion (Misc queries) | |||
How can I print long lists of data from an Excel spreadsheet? | Excel Discussion (Misc queries) | |||
Refreshing Access data in Excel | Excel Discussion (Misc queries) | |||
Copying an Excel spreadsheet to a Word document | Excel Discussion (Misc queries) |