Copy cell range to another sheet

A

Albert

Hi Guys,

I am looking for some help.

I have a macro that I have adapted from Ron de Bruin. Instead of setting the
SourceRange explicitly I would like to code something like this:

Select Sheets("List").Range("e2") then select until the column is empty and
use that as my SourceRange.

Sub copy_1()
Dim SourceRange As Range
Dim DestRange1 As Range
Dim DestRange2 As Range
Dim DestRange3 As Range
Dim DestRange4 As Range
Dim DestSheet1 As Worksheet, Lr As Long
Dim DestSheet2 As Worksheet
Dim DestSheet3 As Worksheet
Dim DestSheet4 As Worksheet

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'fill in the Source Sheet and range
Set SourceRange = Sheets("Stock Summary").Range("A4:e55")

'Fill in the destination sheet and call the LastRow
'function to find the last row
Set DestSheet1 = Sheets("Opening Stock")
Lr = LastRow(DestSheet1)
Set DestSheet2 = Sheets("Closing Stock")
Lr = LastRow(DestSheet2)
Set DestSheet3 = Sheets("Purchases")
Lr = LastRow(DestSheet3)
Set DestSheet4 = Sheets("Usage")
Lr = LastRow(DestSheet4)

'With the information from the LastRow function we can
'create a destination cell and copy/paste the source range
Set DestRange1 = DestSheet1.Range("A2")
SourceRange.Copy DestRange1
Set DestRange2 = DestSheet2.Range("A2")
SourceRange.Copy DestRange2
Set DestRange3 = DestSheet3.Range("A2")
SourceRange.Copy DestRange3
Set DestRange4 = DestSheet4.Range("A2")
SourceRange.Copy DestRange4

' Set DestRange1 = DestSheet1.Range("A" & Lr + 1)
' SourceRange.Copy DestRange1
' Set DestRange2 = DestSheet2.Range("A" & Lr + 1)
' SourceRange.Copy DestRange2
' Set DestRange3 = DestSheet3.Range("A" & Lr + 1)
' SourceRange.Copy DestRange3
' Set DestRange4 = DestSheet4.Range("A" & Lr + 1)
' SourceRange.Copy DestRange4

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

Thanks
Albert
 
S

Simon Lloyd

Do you want to set the source range as a single cell? or a range o
cells? Do you want the user to be able to specify the range perhaps wit
an inputbox or userform

What is it you are trying to do


Hi Guys

I am looking for some help

I have a macro that I have adapted from Ron de Bruin. Instead o
setting th
SourceRange explicitly I would like to code something like this

Select Sheets("List").Range("e2") then select until the column is empt
an
use that as my SourceRange

VBA Code:
--------------------
Sub copy_1(
Dim SourceRange As Rang
Dim DestRange1 As Rang
Dim DestRange2 As Rang
Dim DestRange3 As Rang
Dim DestRange4 As Rang
Dim DestSheet1 As Worksheet, Lr As Lon
Dim DestSheet2 As Workshee
Dim DestSheet3 As Workshee
Dim DestSheet4 As Workshee

With Applicatio
.ScreenUpdating = Fals
.EnableEvents = Fals
End Wit

'fill in the Source Sheet and rang
Set SourceRange = Sheets("Stock Summary").Range("A4:e55"

'Fill in the destination sheet and call the LastRo
'function to find the last ro
Set DestSheet1 = Sheets("Opening Stock"
Lr = LastRow(DestSheet1
Set DestSheet2 = Sheets("Closing Stock"
Lr = LastRow(DestSheet2
Set DestSheet3 = Sheets("Purchases"
Lr = LastRow(DestSheet3
Set DestSheet4 = Sheets("Usage"
Lr = LastRow(DestSheet4

'With the information from the LastRow function we ca
'create a destination cell and copy/paste the source rang
Set DestRange1 = DestSheet1.Range("A2"
SourceRange.Copy DestRange
Set DestRange2 = DestSheet2.Range("A2"
SourceRange.Copy DestRange
Set DestRange3 = DestSheet3.Range("A2"
SourceRange.Copy DestRange
Set DestRange4 = DestSheet4.Range("A2"
SourceRange.Copy DestRange

' Set DestRange1 = DestSheet1.Range("A" & Lr + 1
' SourceRange.Copy DestRange
' Set DestRange2 = DestSheet2.Range("A" & Lr + 1
' SourceRange.Copy DestRange
' Set DestRange3 = DestSheet3.Range("A" & Lr + 1
' SourceRange.Copy DestRange
' Set DestRange4 = DestSheet4.Range("A" & Lr + 1
' SourceRange.Copy DestRange

With Applicatio
.ScreenUpdating = Tru
.EnableEvents = Tru
End Wit

End Su

--------------------
Thank
Albert


--
Simon Lloyd

Regards
Simon Lloy
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)
 
P

PY & Associates

Hi Guys,

I am looking for some help.

I have a macro that I have adapted from Ron de Bruin. Instead of setting the
SourceRange explicitly I would like to code something like this:

Select Sheets("List").Range("e2") then select until the column is empty and
use that as my SourceRange.

Sub copy_1()
    Dim SourceRange As Range
    Dim DestRange1 As Range
    Dim DestRange2 As Range
    Dim DestRange3 As Range
    Dim DestRange4 As Range
    Dim DestSheet1 As Worksheet, Lr As Long
    Dim DestSheet2 As Worksheet
    Dim DestSheet3 As Worksheet
    Dim DestSheet4 As Worksheet

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'fill in the Source Sheet and range
    Set SourceRange = Sheets("Stock Summary").Range("A4:e55")

    'Fill in the destination sheet and call the LastRow
    'function to find the last row
    Set DestSheet1 = Sheets("Opening Stock")
    Lr = LastRow(DestSheet1)
    Set DestSheet2 = Sheets("Closing Stock")
    Lr = LastRow(DestSheet2)
    Set DestSheet3 = Sheets("Purchases")
    Lr = LastRow(DestSheet3)
    Set DestSheet4 = Sheets("Usage")
    Lr = LastRow(DestSheet4)

    'With the information from the LastRow function we can
    'create a destination cell and copy/paste the source range
    Set DestRange1 = DestSheet1.Range("A2")
    SourceRange.Copy DestRange1
    Set DestRange2 = DestSheet2.Range("A2")
    SourceRange.Copy DestRange2
    Set DestRange3 = DestSheet3.Range("A2")
    SourceRange.Copy DestRange3
    Set DestRange4 = DestSheet4.Range("A2")
    SourceRange.Copy DestRange4

'    Set DestRange1 = DestSheet1.Range("A" & Lr + 1)
'    SourceRange.Copy DestRange1
'    Set DestRange2 = DestSheet2.Range("A" & Lr + 1)
'    SourceRange.Copy DestRange2
'    Set DestRange3 = DestSheet3.Range("A" & Lr + 1)
'    SourceRange.Copy DestRange3
'    Set DestRange4 = DestSheet4.Range("A" & Lr + 1)
'    SourceRange.Copy DestRange4

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

Thanks
Albert
Click to expand...

Something like this?

With Sheets("List")
lr = .Range("e2").End(xlDown).Row
Set sourcerng = .Range("e2:e" & lr)
End With
 
A

Albert

Hi Simon,
All I want to do is create a range and paste it to another worksheet.

The range is dynamic as if the list changes the the selection range will
change.

So the selection to copy will start in cell "E2" and stop at the end of the
data in column "E" ( a soon as there is a blank cell which will denote the
end of the range)

Hope that helps.

Thanks
Albert
Simon Lloyd said:
Do you want to set the source range as a single cell? or a range of
cells? Do you want the user to be able to specify the range perhaps with
an inputbox or userform?

What is it you are trying to do?



Hi Guys,

I am looking for some help.

I have a macro that I have adapted from Ron de Bruin. Instead of
setting the
SourceRange explicitly I would like to code something like this:

Select Sheets("List").Range("e2") then select until the column is empty
and
use that as my SourceRange.
Click to expand...
VBA Code:
--------------------


Sub copy_1()
Dim SourceRange As Range
Dim DestRange1 As Range
Dim DestRange2 As Range
Dim DestRange3 As Range
Dim DestRange4 As Range
Dim DestSheet1 As Worksheet, Lr As Long
Dim DestSheet2 As Worksheet
Dim DestSheet3 As Worksheet
Dim DestSheet4 As Worksheet

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'fill in the Source Sheet and range
Set SourceRange = Sheets("Stock Summary").Range("A4:e55")

'Fill in the destination sheet and call the LastRow
'function to find the last row
Set DestSheet1 = Sheets("Opening Stock")
Lr = LastRow(DestSheet1)
Set DestSheet2 = Sheets("Closing Stock")
Lr = LastRow(DestSheet2)
Set DestSheet3 = Sheets("Purchases")
Lr = LastRow(DestSheet3)
Set DestSheet4 = Sheets("Usage")
Lr = LastRow(DestSheet4)

'With the information from the LastRow function we can
'create a destination cell and copy/paste the source range
Set DestRange1 = DestSheet1.Range("A2")
SourceRange.Copy DestRange1
Set DestRange2 = DestSheet2.Range("A2")
SourceRange.Copy DestRange2
Set DestRange3 = DestSheet3.Range("A2")
SourceRange.Copy DestRange3
Set DestRange4 = DestSheet4.Range("A2")
SourceRange.Copy DestRange4

' Set DestRange1 = DestSheet1.Range("A" & Lr + 1)
' SourceRange.Copy DestRange1
' Set DestRange2 = DestSheet2.Range("A" & Lr + 1)
' SourceRange.Copy DestRange2
' Set DestRange3 = DestSheet3.Range("A" & Lr + 1)
' SourceRange.Copy DestRange3
' Set DestRange4 = DestSheet4.Range("A" & Lr + 1)
' SourceRange.Copy DestRange4

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub



Thanks
Albert


--
Simon Lloyd

Regards,
Simon Lloyd
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=186569

Excel Live Chat

.
Click to expand...
 
A

Albert

Hi,

Brilliant. Works like a charm except has copied the formula.

How do I get it just to paste the values?

Thanks
Albert
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy to Next Blank Row 5
copy/paste in the next column 4
Simple Copy/Paste Question 5
Loop 2
Loop Macro 3
this code crashes excel. How come? 3
Code copies ONLY last j from sheet 9
Copy an array of Sheets 4

Top