Copy & Paste Using Defined Name Ranges

[bloodmilksky]

Hi Guys,

I was just wondering if anyone knows how I can adjust the below code so it will

• Copy Cells B21 & C21 On sheet 1
• To A Range Defined in B7
• The range is on sheet 2 and is the users name

Sub CopySheet1A1toSheet2NextCellInColumnA() Dim LastRowSheet2 As Long LastRowSheet2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Sheet2").Cells(LastRowSheet2 - [Sheet2!A1<>""], "A").Value = [Sheet1!A1]End Sub

please any help would be greatly appreciated

many thanks

jamie

 

[gallen]

Breaking down your request in to steps (I've ignored the code you had in there originally):

Sub CopySheet1A1toSheet2NextCellInColumnA()

Dim LastRowSheet2 As Long
Dim sRangeName As String


    'Copy Cells B21 & C21 On sheet 1
    Sheets("Sheet1").Range("B21:C21").Copy
    'To A Range Defined in B7
    sRangeName = Sheets("Sheet1").Range("B7").Value
    'The range is on sheet 2 and is the users name
    Sheets("Sheet2").Range(sRangeName).PasteSpecial xlPasteAll
    
    
    LastRowSheet2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
    Sheets("Sheet2").Cells(LastRowSheet2 - [Sheet2!A1<>""], "A").Value = [Sheet1!A1]


End Sub

 

[bloodmilksky]

THANK You thats awesome.

I was just wondering is there a way that you could do it as with the original code so the next time you went to copy and paste into that range it would identify that there is a value already their and move down to the row below and paste it and so on.

 

[gallen]

Ok for example, what value would be in cell B7 on sheet 1?

 

[bloodmilksky]

Sheet 1 B7 would be Barry Johnson

and then the range on sheet 2 would be Barry_Johnson which would be cells b2:c33

 

[gallen]

Ah. So you want the next available row within that range or the next available row below that range?

Sorry if I sound stupid, it's difficult when you don't see the actual workbook.

 

[bloodmilksky]

next available row within the range

thats okay

 

[bloodmilksky]

if it helps I can email the workbook to you

 

[gallen]

Not sure what this line is trying to achieve:

Sheets("Sheet2").Cells(LastRowSheet2 - [Sheet2!A1<>""], "A").Value = [Sheet1!A1]

However this is tested and working based on your info:

Sub CopySheet1A1toSheet2NextCellInColumnA()

Dim lRow As Long
Dim sRangeName As String

    'Copy Cells B21 & C21 On sheet 1
    Sheets("Sheet1").Range("B21:C21").Copy
    'To A Range Defined in B7
    sRangeName = Sheets("Sheet1").Range("B7").Value
    'The range is on sheet 2 and is the users name


    'get next empty row in named range
    lRow = 1
    Do Until Sheets("Sheet2").Range(sRangeName).Cells(lRow, 1) = ""
        lRow = lRow + 1
    Loop
    'Paste Data
    Sheets("Sheet2").Range(sRangeName).Cells(lRow, 1).PasteSpecial xlPasteAll

End Sub

 

[gallen]

if it helps I can email the workbook to you

If this doesn't work...