Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 414
Default Copy many sheets into one twist

So I have to copy 173 sheets into one list. I found the code at:

http://www.rondebruin.nl/copy2.htm

to be very helpful. I'm using the copydatawithoutheaders macro

But I need to grab information from cell B5 of each sheet and add it to the
end of the list for each item. That is if it copies 4 rows and 5 columns, I
want each row to have a 6th column with the value in cell B5 of that sheet.

The AppendDataAfterLastColumn macro doesn't do what I want, it adds the
information into a new column (or maybe I'm modifying it incorrectly).

Any help would be greatly appreciated! Thanks in advance.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Copy many sheets into one twist

please send your workbook to me
my



"Andy" wrote:

So I have to copy 173 sheets into one list. I found the code at:

http://www.rondebruin.nl/copy2.htm

to be very helpful. I'm using the copydatawithoutheaders macro

But I need to grab information from cell B5 of each sheet and add it to the
end of the list for each item. That is if it copies 4 rows and 5 columns, I
want each row to have a 6th column with the value in cell B5 of that sheet.

The AppendDataAfterLastColumn macro doesn't do what I want, it adds the
information into a new column (or maybe I'm modifying it incorrectly).

Any help would be greatly appreciated! Thanks in advance.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Copy many sheets into one twist

please paste this code to visual basic edit

Const END_COLUMN = 6
Const START_ROW = 8


Private Sub GetProductOrder(sht As Excel.Worksheet, rng As Excel.Range)
Dim intI As Long
Dim intJ As Long
For intI = START_ROW To sht.UsedRange.Rows.Count
If sht.UsedRange.Cells(intI, 1) < "" And sht.UsedRange.Cells(intI,
2) < "" Then
For intJ = 1 To END_COLUMN
rng(1, intJ).Value = sht.UsedRange.Cells(intI, intJ).Value
Next
Set rng = rng.Offset(1, 0)
End If

Next
End Sub

Public Sub MergeData()
ClearData
Dim intI As Integer
Dim rng As Excel.Range
Set rng = Worksheets("MergeSheet").Range("A2:F2")

For intI = 1 To Worksheets.Count
If LCase(Worksheets(intI).Name) < "MergeSheet" Then
Call GetProductOrder(Worksheets(intI), rng)
End If
Next
MsgBox "Merge data complete!"
End Sub

Private Sub ClearData()
Dim sht As Worksheet
Set sht = Worksheets("MergeSheet")
Dim rng As Excel.Range
Set rng = sht.UsedRange
Set rng = rng.Offset(1, 0)
rng.EntireRow.Clear
End Sub


"Andy" wrote:

So I have to copy 173 sheets into one list. I found the code at:

http://www.rondebruin.nl/copy2.htm

to be very helpful. I'm using the copydatawithoutheaders macro

But I need to grab information from cell B5 of each sheet and add it to the
end of the list for each item. That is if it copies 4 rows and 5 columns, I
want each row to have a 6th column with the value in cell B5 of that sheet.

The AppendDataAfterLastColumn macro doesn't do what I want, it adds the
information into a new column (or maybe I'm modifying it incorrectly).

Any help would be greatly appreciated! Thanks in advance.

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
move or copy sheets doesn't copy format ColinX Excel Worksheet Functions 1 May 14th 08 10:07 PM
Function To Add across Multiple Sheets with a Twist Sean Excel Worksheet Functions 11 August 8th 07 10:48 PM
in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? Daniel Excel Worksheet Functions 1 July 6th 05 09:57 PM
Summing across multiple sheets with a twist hillmic Excel Worksheet Functions 5 June 27th 05 04:56 PM
Copy a Formula down with a Twist Q John Excel Worksheet Functions 7 November 23rd 04 11:03 PM


All times are GMT +1. The time now is 11:43 AM.

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"