Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Es
 
Posts: n/a
Default macro to copy columns to sheet

I have got a workbook wiht 10 sheets that I want to make
a summary of on a new shteet

I need a macro to copy Range B1:C up to where the cells
with data ends in the first sheet to the new sheet then
go back to the next sheet and copy the same range (the
amount of lines can differ)and paste where in the frist
sell with no data underneath the first copy paste data
Hope I make sense. The sheets has got diferendt names
like Floraland, JFK, Franay.
  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

Maybe this little macro will get you closer to what you want.

Option Explicit
Sub testme()
Dim mySelectedSheets As Sheets

Dim wks As Worksheet
Dim RngToCopy As Range

Dim newWks As Worksheet
Dim DestCell As Range

Set mySelectedSheets = ActiveWindow.SelectedSheets
ActiveSheet.Select

Set newWks = Worksheets.Add
Set DestCell = newWks.Range("a1")

For Each wks In mySelectedSheets
With wks
Set RngToCopy = .Range("b1", .Cells(.Rows.Count, "c").End(xlUp))

RngToCopy.Copy _
Destination:=DestCell
With newWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
End With
Next wks
End Sub

It copies B1:C(last used cell in C) to a new sheet for each of the grouped
sheets.

So select the first sheet, then ctrl-click on the tabs of the other sheets you
want.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Es wrote:

I have got a workbook wiht 10 sheets that I want to make
a summary of on a new shteet

I need a macro to copy Range B1:C up to where the cells
with data ends in the first sheet to the new sheet then
go back to the next sheet and copy the same range (the
amount of lines can differ)and paste where in the frist
sell with no data underneath the first copy paste data
Hope I make sense. The sheets has got diferendt names
like Floraland, JFK, Franay.


--

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
MACRO - copy rows based on value in column to another sheet Michael A Excel Discussion (Misc queries) 1 March 5th 05 03:15 AM
Can a macro format a hidden sheet? Robert Excel Discussion (Misc queries) 1 February 9th 05 07:13 PM
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. RonMc5 Excel Discussion (Misc queries) 9 February 3rd 05 01:51 AM
Copy formula...sheet 2 sheet Mick New Users to Excel 0 January 26th 05 02:58 AM
Need Macro to copy specific sheet mac Excel Worksheet Functions 1 January 17th 05 09:46 PM


All times are GMT +1. The time now is 06:24 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"