Transferring Data Between Sheets (Excel VBA)

[ShiGuy]

Hi all,

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.
Shi

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Sub transfer()
Dim strSourceSheet As String, strDestinationSheet As String, sourceData As String
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</code>

 

[bhos123]

May be it's a hidden sheet.

add <code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Sheets(strDestinationSheet).Visible= True</code>

 

[GTO]

Greetings and welcome to MrExcel

You do not need to un-hide and/or activate the worksheet to read a cell's value into a variable. Try:

strSourceSheet = "profile list"
SourceData = ThisWorkbook.Worksheets(strSourceSheet).Cells(30, 37).Value

BTW, Sheets(strDestinationSheet).Cells(C,11)= sourceData will fail unless 'C' is a variable you have declared and assigned a numeric value someplace not shown. If you meant row 11 in Column C, it could be:

Sheets(strDestinationSheet).Cells(11,"C")= sourceData

Hope that helps,

Mark

 

[amkumar]

Hi shi,

If you need transfer data From one sheet(“Mastersheet”) to another sheet (Sub) then we can use below syntax for complete the task.

Please Rename

Data sheet=”Master”
Another sheet =“Sub”

Sub DataCopyToAnotherSheet()
ThisWorkbook.Worksheets("Master").UsedRange.Copy ThisWorkbook.Worksheets("Sub").Range("A1")
End sub


If any problem than please reach at Sachin.maxrathore@gmail.com

Thanks

 

[GTO]

Cross-posted Here

ShiGuy, please read this article

Mark

 

[dmt32]

Cross-posted Here

ShiGuy, please read this article

Mark

You missed one - Here as well:Transferring Data Between Sheets (Excel VBA)

 

[ShiGuy]

Apologies, I won't cross-reference unless absolutely needed anymore!

 

[ShiGuy]

Thanks so much! I made both the source and destination visible and it worked!

May be it's a hidden sheet.

add <code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Sheets(strDestinationSheet).Visible= True</code>