Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
DavidB
 
Posts: n/a
Default copying data from Excel spreadsheet to another

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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
Fetching External Data from Excel Sri Excel Discussion (Misc queries) 2 January 3rd 05 12:46 PM
How can I print long lists of data from an Excel spreadsheet? sonic_d_hog Excel Discussion (Misc queries) 1 December 22nd 04 10:31 PM
Refreshing Access data in Excel Jimmy G Excel Discussion (Misc queries) 0 December 21st 04 04:53 PM
Copying an Excel spreadsheet to a Word document KG Excel Discussion (Misc queries) 1 November 28th 04 08:38 PM


All times are GMT +1. The time now is 01:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"