Excel - Combine Two Small Macros 1

[Salut39]

Hi, I am trying combine two macros together it works until '*? point and breaks. Since I don't know VBA I am struggling to fix it. Any one can help? I need this sheet select Q1 and copy it to A1.


Private Sub CommandButton1_Click()

Sheets("001").Copy Before:=Sheets(1)
Sheets(1).Name = Format(Sheets.Count - 5, "000")
'*?
Range("Q1").Select
Selection.Copy
Range("A1:B1").Select
ActiveSheet.Paste
Range("A3").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End Sub

Thanks
Yuri

 

[SkipVought]

hi,

" I need this sheet select Q1 and copy it to A1."

On what sheet?

    YourSheetObject.Range("Q1").Copy
    YourSheetObject.Range("A1:B1").Paste
    YourSheetObject.Range("A3").Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Salut39]

Hi Skip,

I am trying to combine first macro which names sheet with the copying bit so I could run it as one.

Fisrt:

Sheets("001").Copy Before:=Sheets(1)
Sheets(1).Name = Format(Sheets.Count - 5, "000")

Second:
Range("Q1").Select
Selection.Copy
Range("A1:B1").Select
ActiveSheet.Paste
Range("A3").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

 

[Salut39]

Second macro should be run on a newly created one after calculation, because cell Q11 will bring me new ProductName from the list which looks up SheetName for reference.

 

[Salut39]

I tried to use formula in A1 : Vlookup(SheetName,...) but SheetName not constant it recalculates all sheet and takes active SheetName to all sheets. Unless there's a way for formula to have a current SheetName fixed, I only see it done with the macro?

 

[SkipVought]

Private Sub CommandButton1_Click()

    Sheets("001").Copy Before:=Sheets(1)
    Sheets(1).Name = Format(Sheets.Count - 5, "000")
    '*?
    With Sheets(1)
        .Range("Q1").Copy
        .Range("A1:B1").PasteSpecial xlPasteAll
        .Range("A3").Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    End With
End Sub

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Salut39]

All works great! Many thanks as always.

Yuri