PDA

View Full Version : Information about Transferring Data In Excel.



thanhvanchi
12-08-2016, 07:57 PM
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
12-08-2016, 08:15 PM
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
12-08-2016, 11:29 PM
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
12-09-2016, 06:36 AM
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.