copy multiple sheets to same destination 3

[smurf01]

I have a work book that has 10 sheets in it and I want to copy the data from sheets 2 to 10 into sheet 1. the data from sheets 2 to 10 is all stored in the same cells (A3:Q200) and i want to copy each sheets data under each other.

i.e. sheet 1 will be in (A3:Q200), sheet 2 will be in (A201:Q400)etc. However my problem is that in sheet 2 the data may only be down to row 191 and then if this is the case i will have some empty rows

what I would like to know is how can I set a macro that pastes the data from sheet 1 then selects the next empty row down and then pastes sheet 2 and the selects the next empty cell down etc.

This is the code i have now

Sub CopySheet()

Sheets("1&quot.Select
Range("A3:Q200&quot.Select
Selection.Copy
Sheets("Summary&quot.Select
Range("A3&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Sheets("2&quot.Select
Range("A3:Q200&quot.Select
Selection.Copy
Sheets("Summary&quot.Select
Range("A201&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


End Sub

Any Help would be much appreciated





Regards

Paul

 

[SkipVought]

Hi,

wsTarget = Activesheet
with wsTarget.[A3].currentregion
  for each ws in worksheets
    if ws.name <> wsTarget.name then
      ws.range(&quot;A3:Q200&quot;).copy _ 
        destination:=wsTarget.cells(.row + .rows.count - 1, 1)
    end if
  next
end with

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[smurf01]

Skip, could you explain where I should place this code, should it be in the Taget worksheet as a private sub

Regards

Paul

 

[SkipVought]

It should be in a module NOT in the worksheet object

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[smurf01]

Skip, I have placed this in a module as shown below
Sub CopySheet()
Dim wksSummary As Worksheet
wsSummary = ActiveSheet
With wsSummary.[A3].CurrentRegion
For Each ws In Worksheets
If ws.Name <> wsSummary.Name Then
ws.Range(&quot;A3:Q200&quot.Copy _
Destination:=wsSummary.Cells(.Row + .Rows.Count - 1, 1)
End If
Next
End With
End Sub


but I keep getting the error message @object does not support this property or method

Regards

Paul

 

[smurf01]

Skip, ignore my previous message i had set Dim as wksSummary and not wsSummary, however I now get the error message as follows

object variable or With block variable not set

Regards

Paul

 

[duncansancho]

The way that I have always handled the problem posed is as follows
Sub CopySheet()

Sheets(&quot;1&quot.Select
Range(&quot;A3&quot.Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets(&quot;Summary&quot.Select
Range(&quot;A3&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Sheets(&quot;2&quot.Select
Range(&quot;A3&quot.Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets(&quot;Summary&quot.Select
Range(&quot;A3&quot.Select
Do Until ActiveCell = &quot;&quot;
ActiveCell.Offset(1,0).Range(&quot;A1&quot.Select
Loop
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


End Sub

Skip's code looks a lot more elegant and I'd love to know how it works, but if you are stuck, mine will work !

 

[smurf01]

duncansancho, I have tried your code but it does not work because when the code gets to &quot;Selection.CurrentRegion.Select&quot; it also selects rows 1 and 2 which i do not want as some of the cells are merged and it will not therefore paste because it says that &quot;this operation requires the merged cells to be identically sized

Regards

Paul

 

[smurf01]

duncansancho, Ok I have got it to work now i just changed &quot;Selection.CurrentRegion.Select&quot; to &quot;Range(&quot;A3:Q200&quot.Select&quot; and it works fine, If I can get Skip's code to work I will post i back here so taht you can see it

Regards

Paul

 

[SkipVought]

sorry, I forgot to use SET and I had a few other errors...

Sub CopySheet()
Dim wksSummary As Worksheet
Set wsSummary = ActiveSheet
  For Each ws In Worksheets
    If ws.Name <> wsSummary.Name Then
      With wsSummary.[A3].CurrentRegion
      ws.Range(&quot;A3:Q200&quot;).Copy _
        Destination:=wsSummary.Cells(.Row + .Rows.Count, 1)
      End With
    End If
  Next
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[smurf01]

Skip, please do not apologise i am very happy that you have taken the time out to try and help me. I like many others always appreciate the help given by the wizards that take time out to respond to what in some cases must be very mundane questions. I will test out the code and let you know how i get on

Regards

Paul

 

[smurf01]

Skip, the code works to a degree when I run it it copies the data from sheet1 into the Summary worksheet but does not copy anything after. I then tried deleting the data it had copied from sheet 1 before it ran the statement agin and then it copied sheet 2 data starting from Cell A3. Itried this a few times with the same response.

i.e. I stepped into the code and after it had copied the data from sheet I then deleted the data stepped through the code agin and this time it copied the data from sheet 2 and so on ??

I am a bit puzzled by this

Regards

Paul

 

[kupe]

Hi Paul

Yes, me too. Skip's doesn't get there, but duncansancho does the trick. A good piece of coding to save for future use. Cheers and thanks, duncansancho

 

[SkipVought]

What's not working correctly?



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[smurf01]

Skip, this is what i sent in an earlier post

Skip, the code works to a degree when I run it it copies the data from sheet1 into the Summary worksheet but does not copy anything after. I then tried deleting the data it had copied from sheet 1 before it ran the statement agin and then it copied sheet 2 data starting from Cell A3. Itried this a few times with the same response.

i.e. I stepped into the code and after it had copied the data from sheet I then deleted the data stepped through the code agin and this time it copied the data from sheet 2 and so on ??

I am a bit puzzled by this

Regards

Paul

 

[SkipVought]

Well what puzzes me is that it works for all the worksheets in my workbook.

What does the data range look like in each of these sheets? This assumes that I have a contiguous range of data.

This statement...

With wsSummary.[A3].CurrentRegion

assumes that referenced on [A3] in the summary sheet, the Current Region will referenc all the data that has been copied.

IF NOT, then another technique can be used -- the UsedRange property...

Sub CopySheet()
Dim wksSummary As Worksheet
Set wsSummary = ActiveSheet
  For Each ws In Worksheets
    If ws.Name <> wsSummary.Name Then
      With wsSummary.UsedRange
        r = .Row
        rc = .Rows.Count
        If r = 1 Then r = 2
      ws.Range(&quot;A3:Q200&quot;).Copy _
        Destination:=wsSummary.Cells(r + rc, 1)
      End With
    End If
  Next
End Sub

Ya know, I don't have a crystal ball ;-)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[smurf01]

Skip, sorry if I mislead you, I have had a good look at my data and I am sorry but I missed something fundemental. after looking at all of my sheets I found that there are some sheets where the data is not filled in down to Row 200 therefore that means that each sheet could have varying amounts of rows, is it possible to cover this situation


Please accept my apologies for the error I do understand that you are trying to help me with this

Regards

Paul

 

[smurf01]

Skip, I have sorted out my problem, it had to do with some empty rows. Your code works absolutly fine now. Again please accept my apologies for misleading you, you deserve a star for your perseverance alone !!!.

Could I ask you one more question relating to formulas in the sheets or should I start a new thread ??

Regards

Paul

 

[SkipVought]

Might be better to start a new thread.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[smurf01]

Skip, you very kindly helped me out with this code even though I threw you a few &quot;red herrings&quot; along the way and the code as it stands below works fine

Sub CopySheet()
Dim wsSummary As Worksheet
Set wsSummary = ActiveSheet
For Each ws In Worksheets
If ws.Name <> wsSummary.Name Then
With wsSummary.[A3].CurrentRegion
ws.Range(&quot;A3:Q200&quot.Copy _
Destination:=wsSummary.Cells(.Row + .Rows.Count, 1)
End With
End If
Next
End Sub

What I would like to know is if it is possible to make the code do a &quot;paste special/values&quot; rather than just copy the data as I would like to omit the formulas in some cells and just have the values.


Regards

Paul