Information about Transferring Data In Excel.

[thanhvanchi]

Hello everyone!

So I'm trying to do a complicated data transfer between one "master" sheet and multiple "sub" sheets.
Today is my first day with Excel VBA so I'm pretty lost haha.
I decided to start small and transfer data from one cell of one sheet to the other sheet but I keep on getting this error of "Cannot jump to 'Sheets' because it is hidden".
Can anyone please lend me a hand? Thanks in advance.

Sub transfer() 
    Dim strSourceSheet AsString, strDestinationSheet AsString, sourceData AsString 
    strSourceSheet ="profile list" 
    Sheets(strSourceSheet).Activate 
    sourceData = Sheets(strSourceSheet).Cells(30,37).Value 
    strDestinationSheet ="RADIANT OPTO-ELECTRONICS CORP." 
    Sheets(strDestinationSheet).Activate 
    Sheets(strDestinationSheet).Cells(C,11)= sourceData 
End Sub

Thank's a lot!

[Paul_Hossler]

You usually do not need to select something to use it.

Note that 'C' does not have a value in the code you showed

Sub transfer_1() 
    Dim strSourceSheet AsString, strDestinationSheet AsString, sourceData AsString 

    strSourceSheet ="profile list" 

    sourceData = Sheets(strSourceSheet).Cells(30,37).Value 

    strDestinationSheet ="RADIANT OPTO-ELECTRONICS CORP." 

    Sheets(strDestinationSheet).Cells(C,11)= sourceData 
End Sub

[jolivanes]

Assume that the C that Paul mentioned is Column C, here are some possibilities.

Sub transfer_1() 
Dim shSource As Worksheet, shDest As Worksheet
Set shSource =Sheets("profile list")
Set shDest = Sheets("RADIANT OPTO-ELECTRONICS CORP.")
shDest.Cells(3, 11).Value = shSource.Cells(30,37).Value    '<---- Values only
shSource.Cells(30,37).Copy shDest.Cells(3, 11)    '<---- All
End Sub

Sub transfer_2() 
Sheets("RADIANT OPTO-ELECTRONICS CORP.").Cells(3, 11).Value = Sheets("profile list").Cells(30,37).Value
End Sub

Sub transfer_3() 
Sheets("profile list").Cells(30,37).Copy Sheets("RADIANT OPTO-ELECTRONICS CORP.").Cells(3, 11)
End Sub

[SamT]

Range Address As String = Column Letter + Row Number. ie: Range("C11")
Cell Address as Variant = Row Number and Column Designator. ie Cells(11, 3) or Cells(11, "C")

Range Addresses can be variables. ie Dest = "C11" >> Range(Dest)
Cell Addresses can use variables. ie Rw = 11, Col = 3 >> Cells(Rw, Col)

Range Addresses can be concatenated. ie Rw = 11 >> Range ("C" & Rw)
Cell Addresses can use Math. i = 10 >> Cells(i + 1, "C")

Very handy concept is "Last Used"

Dim LastCell As Range
Set LastCell = Cells(Rows.Count, "C").End(xlUp)

LastCell will be the bottom most used Cell in Column C.

.End(xlUp) is the same as Pressing Ctrl+Up arrow on your keyboard. .End can be modified with (xlUp), (xlDown), (xlToRight), and (xlToLeft), representing each Arrow key.

This is most often seen as LastRow

Dim LastRow As Long
LastRow = Cells(Rows.Count, "C").End(xlUp).Row

LastRow will be the Row number of the bottom most used Cell in Column C

Warning: Always Declare Row and Column counters as Longs. Integers just aren't large enough to handle all available Rows and Columns.