Saving copied cell for later use

K

kl88

New Member
Joined
Jun 30, 2011
Messages
15
Heya,

I was wondering if its possible to name a cell, like for temporary use in the macro. I have a cell at the beginning of the macro which i need to copy later in the macro. Here under is the code i have, as u can see i tried to name the cell "Behandelaar and call it up later but obviously it wont work like that. I need the cell i tried to call behandelaar later copied into the selection (see 'Fill in Behandelaar on all selected SBRS). I hope someone can help ! if i'm not clear enough bout the problem, let me know !!
Thnx !

Code: 
Sub ImportFile()
'Macro to import file into the sheet
'Turn of any filter
If ActiveSheet.AutoFilterMode Then
    If ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
    End If
ElseIf ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
End If
'Bring up userform Behandelaar
frmBehandelaar.Show
'In case the userform isnt filled in(cancelled) the macro will cease
If Range("Z" & ActiveCell.Row) = "" Then
Range("A1").Select
'In case the userform is filled in the macro will continue
Else
ActiveCell = Behandelaar
'Select first empty cel in B column
    If IsEmpty(Range("B2")) Then
    Range("B2").Select
    Set Imputhere = ActiveCell
    Else
    Range("B1").End(xlDown).Select
    Selection.Offset(1, 0).Select
    Set Imputhere = ActiveCell
    End If
'Open worksheet that has to be imported
Dim FD As Object
    Set FD = Application.FileDialog(msoFileDialogOpen)
    Set finalfile = ThisWorkbook
    With FD
        .Title = "Select SBR"
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewDetails
        .Filters.Add "SBR File", "*.xls*", 1
        .FilterIndex = 1
        .ButtonName = "Select"
        If .Show = -1 Then
            Workbooks.Open FD.SelectedItems.Item(1), 0, True
        Else
            Exit Sub
        End If
    End With
'Copying inputsheet into finalfile
    Set inputfile = ActiveWorkbook
    Sheets("ZVDC").Select
    Sheets("ZVDC").Copy before:=finalfile.Sheets(1)
'Shutdown window inputfile
    inputfile.Activate
    ActiveWorkbook.Close
'Select all Customer (SAP) Codes
    Cells.Find(What:="Customer (SAP) Code", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Select
        ActiveCell.Offset(1, 0).Select
'Select only cells with actual value (= no formulas)
        Dim LR As Long, cell As Range, rng As Range
        With ActiveSheet
            LR = .Range("A" & Rows.Count).End(xlUp).Row
            For Each cell In .Range("A2:A1000" & LR)
                If cell.Value <> "" Then
                    If rng Is Nothing Then
                        Set rng = cell
                    Else
                        Set rng = Union(rng, cell)
                    End If
                End If
            Next cell
            rng.Copy
        End With
'Place Codes into finalsheet
    Sheets(2).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Selection.NumberFormat = "0;-0;;@"
'V-lookup Material Code (Compulsory)
    Selection.Offset(0, 1).Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-1],ZVDC!R2C1:R200C18,2,FALSE)"
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0;-0;;@"
'V-lookup Quantity / # of Units
    Selection.Offset(0, 1).Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-2],ZVDC!R2C1:R200C18,3,FALSE)"
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0;-0;;@"
'V-lookup Condition type
    Selection.Offset(0, 1).Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-3],ZVDC!R2C1:R200C18,4,FALSE)"
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0;-0;;@"
'V-lookup Value per Unit
    Selection.Offset(0, 1).Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-4],ZVDC!R2C1:R200C18,5,FALSE)"
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0;-0;;@"
'V-lookup Item Description to appear next to each invoice value
    Selection.Offset(0, 1).Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-5],ZVDC!R2C1:R200C18,6,FALSE)"
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0;-0;;@"
'V-lookup Performance Date
    Selection.Offset(0, 1).Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-6],ZVDC!R2C1:R200C18,7,FALSE)"
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0;-0;;@"
'V-lookup Cost Center
    Selection.Offset(0, 1).Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-7],ZVDC!R2C1:R200C18,8,FALSE)"
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0;-0;;@"
'V-lookup Info for Sales order only - text 1
    Selection.Offset(0, 1).Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-8],ZVDC!R2C1:R200C18,9,FALSE)"
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0;-0;;@"
'V-lookup Info for Sales order only - text 2
    Selection.Offset(0, 1).Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-9],ZVDC!R2C1:R200C18,10,FALSE)"
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0;-0;;@"
'V-lookup Text to appear on the Invoice below item description
    Selection.Offset(0, 1).Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-10],ZVDC!R2C1:R200C18,11,FALSE)"
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0;-0;;@"
'V-lookup Product line
    Selection.Offset(0, 1).Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-11],ZVDC!R2C1:R200C18,12,FALSE)"
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0;-0;;@"
'V-lookup Text to appear on the Revenue account in SAP
    Selection.Offset(0, 6).Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-17],ZVDC!R2C1:R200C18,13,FALSE)"
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0;-0;;@"
'V-lookup Main Dealer NL Code - if needed to find SAP code
    Selection.Offset(0, 2).Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-19],ZVDC!R2C1:R200C18,15,FALSE)"
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0;-0;;@"
'V-lookup GFIMS Code
    Selection.Offset(0, 1).Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-20],ZVDC!R2C1:R200C18,16,FALSE)"
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0;-0;;@"
'V-lookup Name
    Selection.Offset(0, 1).Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-21],ZVDC!R2C1:R200C18,17,FALSE)"
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0;-0;;@"
'V-lookup City
    Selection.Offset(0, 1).Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-22],ZVDC!R2C1:R200C18,18,FALSE)"
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0;-0;;@"
'Fill in Behandelaar on all selected SBRS
    Selection.Offset(0, 2).Select
    Behandelaar.Select
    
'Select first free cell in B
    Range("B1").End(xlDown).Select
    Selection.Offset(1, 0).Select
    
'Verwijder tijdelijke ZVDC sheet zonder toestemming
    Application.DisplayAlerts = False
    Sheets("ZVDC").Select
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
End If
End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
You can actually name a cell, but your syntax needs to be correct:
ActiveCell.Name = "Behandelaar"

When you want to refer to the cell later on:
Range("Behandelaar").Select
And so on...
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
229
kbk
K
Dazzawm
  • Solved
Change Range To Recorded Macro
Replies
3
Views
169
Alex Blakenburg
A
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
390
willow1985
willow1985
R
  • Question Question
I keep getting the error 91 but i cant figure out what is wrong here.
Replies
2
Views
57
mumps
mumps
D
  • Solved
extract to another workbook and save using desktop path
2
Replies
12
Views
205
jolivanes
jolivanes

Forum statistics

Threads
1,224,587
Messages
6,179,735
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top