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

 

[bloodmilksky]

it works but the one problem that I have is the user name is generating with a space inbetween the first and last name but the named range has a underscore. is there a way that you could factor this in to the code?

many thanks

jamie

 

[gallen]

Try this: line added to replace any spaces with underscore:

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
    'replace any spaces with underscore:
    [COLOR=#ff0000]sRangeName = Replace(sRangeName, " ", "_")[/COLOR]
    
    
    '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

 

[bloodmilksky]

It works perfect thank you Gallen

I was just wondering if I could ask you about automating this or even if it is possible?

so the usersname would be in A1 and the cells To Copy would be in B1 & C1

and it would basically just run through the list copying them and pasting them into
named ranges on Sheet 2?

I am really sorry for asking but seeing what is possible just gives me more ideas.

many thanks

Jamie

 

[bloodmilksky]

Hi,

is there any chance that this can be done with columns instead of cells ? so it would copy B1:C1 based on the Name in A1?

 

[gallen]

So what for example, would be the values in B1 and C1?

 

[bloodmilksky]

Hi,

so it would be something like the below

COLUMNA B C

ABC	456	789
DEF	123	456
GHI	789	1011
JKL	1213	1415
MNO	1617	1819

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

with the named ranges on sheet 2 would be the values in column A

 

[bloodmilksky]

Does that make sense?

 

[gallen]

Sorry, snowed under at work. Your example is confusing as the formatting went astray.

Based on this:

so it would copy B1:C1 based on the Name in A1?

I asked what would the value like be in B1:C1?

I think based on our previous solution, you wish to loop through all names in column A starting at cell A1. Then provide the solution I originally provided for each cell that has a value in column A? Is this correct?

 

[bloodmilksky]

YES PERFECT

Sorry for the bad explanation trying to multitask.

 

[gallen]

Just tried it and I'm still lost. Not enough info.

so this code:

'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
    'replace any spaces with underscore:

    sRangeName = Replace(sRangeName, " ", "_")

Where we copy B21:C21 and place it in B7, where would we paste it now?