Finding the error on my code. Nothing happens with it

[dmalvareg]

Dear all,

I'm a newbe in this macro world and I have been trying to get my routine working. I don't have any error apparently, but the issue is that I'm not copying anything and that's exactly what I need to do. I checked names, ranges and everyting is fine. Could you please help me?

This is the code:
sht0 exist of course, as well as Template and they have the specified tabs!

Dim cell As Range
sht0.Activate
For i = 1 To 10
With sht0
.Worksheets("Online Data").Select
If Cells(10, i).Value = "Time" Then
Columns(i).Select
Selection.Copy
Template.Activate
With Template
.Worksheets("Fermentation").Select
Range("E14").Select
Selection.ActiveSheet.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End With
End If
End With
Next i

Looking forward for your comments!

 

[mumps]

You haven't defined what sht0 and Template are. It appears that they are two separate workbooks. Is this true? Also, do you want to copy and paste the entire column or just starting from row 10 down to the bottom of your data?

 

[dmalvareg]

Hi mumps,
Thanks for your answer. I previously defined template and sht0 as worksheets , I have already use them in a previously i the code and indeed they are separated workbooks. Basically, what I need is a really "simple" task, I need to copy the column below the header "Time" and paste special (transpose) it in a different workbook.

Thanks in advance!

 

[Jonmo1]

You need to qualify the Cells, Columns and Range commands.
Since you're using a With structure, that means putting a period before them.

.Cells(
.Columns
.Range(


And you don't need to select ranges to copy/paste them

Columns(i).Select
Selection.Copy
Should just be
.Columns(i).Copy

And

.Worksheets("Fermentation").Select
Range("E14").Select
Selection.ActiveSheet.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Can be
.Worksheets("Fermentation").Range("E14").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

 

[mumps]

Thank you for the rep. Make sure that both workbooks are open. Place the macro below in a regular module in your source workbook and run it from there. In the code, replace "full name of destination workbook here" with the actual name of your destination workbook including the file extension: for example:

Workbooks("Template.xlsx").Sheets("Fermentation").Range("E14").PasteSpecial Transpose:=True

This is the macro:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Online Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim foundTime As Range
    Set foundTime = Sheets("Online Data").Rows(10).Find("Time", LookIn:=xlValues, lookat:=xlWhole)
    If Not foundTime Is Nothing Then
        Sheets("Online Data").Range(Cells(11, foundTime.Column), Cells(LastRow, foundTime.Column)).Copy
        Workbooks("full name of destination workbook here").Sheets("Fermentation").Range("E14").PasteSpecial Transpose:=True
    End If
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[dmalvareg]

Thanks for your help!
But, still is not working
The macro is working without problems, but nothing is copied in Ferentation tab in the template workbook!

Basically this is what I have!

Sub DataTransferFermentation()
'Copy "overview" tap information


Dim Strain As String
Dim Number As String
Dim Dat As Date
Dim Owner As String
Dim Template As Workbook
Dim sht0 As Workbook
Dim wkbName As String


Set sht0 = ActiveWorkbook
wkbName = ActiveWorkbook.Name
sht0.Worksheets(1).Select
Strain = Range("E12")
Number = Range("E8")
Dat = Range("E11")
Owner = Range("E10")


Set Template = Workbooks.Open("D:\Data\alvarezd\BusinessDocuments\Data Management\Fermentation process data\Insilico macro files\Process.Data.Inspector.xlsx")


Template.Worksheets(1).Select
Worksheets(1).Range("C25") = Strain
Worksheets(1).Range("C27") = Number
Worksheets(1).Range("C21") = Owner
Worksheets(1).Range("C28") = Dat
Selection.NumberFormat = "m/d/yyyy"


'Copy sampling times for DIONEX


'Template.SaveAs Filename:=wkbName, FileFormat:=xlWorkbookNormal 'Save it at the end, and close first the original file


Application.ScreenUpdating = False
Dim LastRow As Long
sht0.Activate
LastRow = Sheets("Online Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim foundTime As Range
Set foundTime = Sheets("Online Data").Rows(10).Find("Time", LookIn:=xlValues, lookat:=xlWhole)
If Not foundTime Is Nothing Then
Sheets("Online Data").Range(Cells(11, foundTime.Column), Cells(LastRow, foundTime.Column)).Copy
Template.Sheets("Fermentation").Range("E14").PasteSpecial Transpose:=True
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True

End sub

The workbook sht0 is open and previously used. The template workbook is open during running ther macro. The macro itself is located in an independent workbook.

Still trying to find the issue, thanks in advance for your input!

 

[dmalvareg]

Small update, now I have error: Run time error '9' Subscript out of range and debugs on
LastRow = Sheets("Online Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Is not recognizing Sheets("Online Data") from sht0 workbook!

Please help, I have tried different strategies, and in every case the problem is on that type of line were I'm calling the sht0 workbook, and this is strange because at the beginning of the macro I called the active workbook sht0, is this not maintained throughout the macro?

 

[Andrew Poulsom]

Does this work for you:

LastRow = sht0.Sheets("Online Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[dmalvareg]

Does this work for you:

LastRow = sht0.Sheets("Online Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Thanks for the correction!
Problem solved! Now back again to the initial issue, nothing is pasted in the "Fermentation" tab, the fisrt part is done but not the copy/paste thing!

 

[Andrew Poulsom]

Maybe foundTime is Nothing. Step through the code to see what's happening.