Hi,

I have 10 identical sheets in a workbook. I have a copy of th
workbook to which i would like to paste certain non-contiguous cell
from the original workbook.

so i would need to loop through all 10 sheets in workbook 1 and cop
certain cells to workbook 2

Can anyone give me a pointer on how to do this using VBA?

Do I need to copy and paste each range seperately or can i use th
union method to create the complete range of cells in each sheet that
wish to copy?

Thanks

F

--
Frigid_Digi
-----------------------------------------------------------------------
Frigid_Digit's Profile: http://www.excelforum.com/member.php...fo&userid=2692
View this thread: http://www.excelforum.com/showthread.php?threadid=40188

I wouldn't even bother with the union method or anything fancy.

If everything is called the same time after time (ie workbook name
sheet names etc.) I'd just use the inbuilt record function and then ru
the macro again whenever you need it.

if names change, a) I'd try and not make them change and stick to th
above. if b) they have to change, then you can work through the cod
and use a variable for each of the things that change which you ca
lookup from a 'control' sheet in the workbook.


e.g.

dim sheet1name as string, sheet2name as string, etc...

sheets("control").select
sheet1name = range("sheet1name").value


'then later in the thing:

replace what the original first sheet name was in the code with th
variable sheet1name



But I'd try a first :

--
gearo
-----------------------------------------------------------------------
gearoi's Profile: http://www.excelforum.com/member.php...fo&userid=2657
View this thread: http://www.excelforum.com/showthread.php?threadid=40188

Thanks for the response

--
Frigid_Digi
-----------------------------------------------------------------------
Frigid_Digit's Profile: http://www.excelforum.com/member.php...fo&userid=2692
View this thread: http://www.excelforum.com/showthread.php?threadid=40188

The second question first--depends on what the discontiguous range is and what
you want copied.

But it never hurts to copy each area separately.

Option Explicit
Sub testme02()

Dim myAddrToCopy As Variant
Dim fwks As Worksheet
Dim rngToCopy As Range
Dim twks As Worksheet
Dim myArea As Range
Dim DestCell As Range

Set twks = Workbooks("other.xls").Worksheets("sheet1")
myAddrToCopy = Array("a1:B9,c18:d92,f5")

For Each fwks In Workbooks("first.xls").Worksheets
With fwks
Set rngToCopy = .Range(myAddrToCopy)
For Each myArea In rngToCopy.Areas
With twks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
myArea.Copy _
Destination:=DestCell
Next myArea
End With
Next fwks

End Sub

This compiled, but I didn't test it.



Frigid_Digit wrote:

Hi,

I have 10 identical sheets in a workbook. I have a copy of the
workbook to which i would like to paste certain non-contiguous cells
from the original workbook.

so i would need to loop through all 10 sheets in workbook 1 and copy
certain cells to workbook 2

Can anyone give me a pointer on how to do this using VBA?

Do I need to copy and paste each range seperately or can i use the
union method to create the complete range of cells in each sheet that I
wish to copy?

Thanks

FD

--
Frigid_Digit
------------------------------------------------------------------------
Frigid_Digit's Profile: http://www.excelforum.com/member.php...o&userid=26921
View this thread: http://www.excelforum.com/showthread...hreadid=401888

--

Dave Peterson

Thanks for the help Dave!


--
Frigid_Digit
------------------------------------------------------------------------
Frigid_Digit's Profile: http://www.excelforum.com/member.php...o&userid=26921
View this thread: http://www.excelforum.com/showthread...hreadid=401888

with VBE place this in "thisworkbook"
and make sure you have a sheet named "Sheet2"

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Dim myrow As Integer
Dim mycolToCopy As Variant
Dim counter As Integer

mycolToCopy = Array(1, 2, 3, 5, 7, 9, 10, 12, 13, 14) 'your columns
to copy

If Sh.Name = "Sheet1" Then
If Target.Column = 1 Then
myrow = Target.Row ' copy to same row on sht 2

With ActiveWorkbook.Sheets("Sheet2")
For counter = 1 To 10 ' NUMBER OF CELLS TO COPY
.Cells(myrow, counter).Value = Sh.Cells(myrow,
mycolToCopy(counter))
Next counter
End With
End If
End If

End Sub

"Dave Peterson" wrote:

The second question first--depends on what the discontiguous range is and what
you want copied.

But it never hurts to copy each area separately.

Option Explicit
Sub testme02()

Dim myAddrToCopy As Variant
Dim fwks As Worksheet
Dim rngToCopy As Range
Dim twks As Worksheet
Dim myArea As Range
Dim DestCell As Range

Set twks = Workbooks("other.xls").Worksheets("sheet1")
myAddrToCopy = Array("a1:B9,c18:d92,f5")

For Each fwks In Workbooks("first.xls").Worksheets
With fwks
Set rngToCopy = .Range(myAddrToCopy)
For Each myArea In rngToCopy.Areas
With twks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
myArea.Copy _
Destination:=DestCell
Next myArea
End With
Next fwks

End Sub

This compiled, but I didn't test it.



Frigid_Digit wrote:

Hi,

I have 10 identical sheets in a workbook. I have a copy of the
workbook to which i would like to paste certain non-contiguous cells
from the original workbook.

so i would need to loop through all 10 sheets in workbook 1 and copy
certain cells to workbook 2

Can anyone give me a pointer on how to do this using VBA?

Do I need to copy and paste each range seperately or can i use the
union method to create the complete range of cells in each sheet that I
wish to copy?

Thanks

FD

--
Frigid_Digit
------------------------------------------------------------------------
Frigid_Digit's Profile: http://www.excelforum.com/member.php...o&userid=26921
View this thread: http://www.excelforum.com/showthread...hreadid=401888

--

Dave Peterson

OOPPSS wrong posting, used yourds to help answer another<:(

"Dave Peterson" wrote:

The second question first--depends on what the discontiguous range is and what
you want copied.

But it never hurts to copy each area separately.

Option Explicit
Sub testme02()

Dim myAddrToCopy As Variant
Dim fwks As Worksheet
Dim rngToCopy As Range
Dim twks As Worksheet
Dim myArea As Range
Dim DestCell As Range

Set twks = Workbooks("other.xls").Worksheets("sheet1")
myAddrToCopy = Array("a1:B9,c18:d92,f5")

For Each fwks In Workbooks("first.xls").Worksheets
With fwks
Set rngToCopy = .Range(myAddrToCopy)
For Each myArea In rngToCopy.Areas
With twks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
myArea.Copy _
Destination:=DestCell
Next myArea
End With
Next fwks

End Sub

This compiled, but I didn't test it.



Frigid_Digit wrote:

Hi,

I have 10 identical sheets in a workbook. I have a copy of the
workbook to which i would like to paste certain non-contiguous cells
from the original workbook.

so i would need to loop through all 10 sheets in workbook 1 and copy
certain cells to workbook 2

Can anyone give me a pointer on how to do this using VBA?

Do I need to copy and paste each range seperately or can i use the
union method to create the complete range of cells in each sheet that I
wish to copy?

Thanks

FD

--
Frigid_Digit
------------------------------------------------------------------------
Frigid_Digit's Profile: http://www.excelforum.com/member.php...o&userid=26921
View this thread: http://www.excelforum.com/showthread...hreadid=401888

--

Dave Peterson