Naming a range

  • Thread starter Thread starter Illuminati
  • Start date Start date
I

Illuminati

I have a such macro :

Private Sub Macro1(arkusz As Worksheet)

arkusz.Range(arkusz.PageSetup.PrintArea).Copy
Sheets("Sheet1").Select
Range("A" & Trim(Str(zLastRow))).Select
Arkusz50.Paste
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:="A1", RefersToR1C1:=Selection
zLastRow = zLastRow +
arkusz.Range(arkusz.PageSetup.PrintArea).Rows.Count
Range("A" & Trim(Str(zLastRow))).Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell

End Sub

However the above macro doesnt work (this part:
ActiveWorkbook.Names.Add Name:="A1", RefersToR1C1:=Selection).
I want to name a range by the value located in the cell A1 from the
copied sheet.

Does anybody knows how to do it?
 
Private Sub Macro1(arkusz As Worksheet)

arkusz.Range(arkusz.PageSetup.PrintArea).Copy
Sheets("Sheet1").Select
Range("A" & Trim(Str(zLastRow))).Select
Arkusz50.Paste
Application.CutCopyMode = False
Selection.Name = "A1"
zLastRow = zLastRow + arkusz.Range(arkusz.PageSetup.PrintArea).Rows.Count
Range("A" & Trim(Str(zLastRow))).Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell

End Sub
 
Private Sub Macro1(arkusz As Worksheet)

arkusz.Range(arkusz.PageSetup.PrintArea).Copy
Sheets("Sheet1").Select
Range("A" & Trim(Str(zLastRow))).Select
Arkusz50.Paste
Application.CutCopyMode = False
Selection.Name = "A1"
zLastRow = zLastRow + arkusz.Range(arkusz.PageSetup.PrintArea).Rows.Count
Range("A" & Trim(Str(zLastRow))).Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell

End Sub

--
__________________________________
HTH

Bob
Click to expand...

Run-time error '1004'

It doesn't work :(
The entered name is not valid.

I tried also sth different:
ActiveWorkbook.Names.Add _
Name:=Range("A1").Value, RefersToR1C1:=Selection

but it always get the value from the cell "A1" from the worksheet in
which the data is pasted, but I want the value from the worksheet from
the data is copied (the macro is run for several worksheets).
 
I am confused by what your code is doing, what is arkusz and Arkusz50, and
whether you want to name the selection by a cell value or a string, but
presuming you want the cell value you need code like

Selection.Name = Worksheet("sheetname").Range("A1").Value
 
I am confused by what your code is doing, what is arkusz and Arkusz50, and
whether you want to name the selection by a cell value or a string, but
presuming you want the cell value you need code like

Selection.Name = Worksheet("sheetname").Range("A1").Value

--
__________________________________
HTH

Bob
Click to expand...

Ok, maybe I should start from my aim :)
This is a macro which is called from another macro. It has to copy the
data from the sheet named Sheet1 and paste it into sheet named
Arkusz50. I want also that selection (range- the data copied) to be
named as the text in the cell A1 from the sheet the data is copied.
The macro is run several times by other macro and on the different
sheets, and I want it to name the range every time differently (from
the actual A1 Cell).
Hope its clear now :)
 
Something like this

Private Sub Macro1(arkusz As Worksheet)

Set rngToCopy = Sheets("Sheet1").Range("A1:A10")
zLastRow = Arkusz50.Range("A1").End(xlDown).Row
Set rngToCopyTo = Arkusz50.Range("A" & zLastRow + 1)
rngToCopy.Copy Arkusz50.Range("A" & zLastRow + 1)
rngToCopyTo.Resize(rngToCopy.Rows.Count).Name = _
Sheets("Sheet1").Range("A1").Value
End Sub
 
Something like this

Private Sub Macro1(arkusz As Worksheet)

Set rngToCopy = Sheets("Sheet1").Range("A1:A10")
zLastRow = Arkusz50.Range("A1").End(xlDown).Row
Set rngToCopyTo = Arkusz50.Range("A" & zLastRow + 1)
rngToCopy.Copy Arkusz50.Range("A" & zLastRow + 1)
rngToCopyTo.Resize(rngToCopy.Rows.Count).Name = _
    Sheets("Sheet1").Range("A1").Value
End Sub

--
__________________________________
HTH

Bob
Click to expand...

Thx a lot, I'll later try how it works :)
 
Back
Top