Last Active Excel column Cell

[Vishalunleashed]

Dear Excel Titans,

I need help in terms of identifying the right syntax for the last column cell. Basically I want to transpose data from sheet to another sheet at the last non blank active column value because I already pasted data to last active row so that leaves me to identify the last active column so I can paste next to it.

Here is the option that I tried but it is not working:

c = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
c = Worksheets("TVI#").Cells(1, Columns.Count).End(xlToLeft).Column
    Worksheets("TVI#").Cells(1, c + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True

Cheers!

MOD EDIT: Please use Code Tags to post Code.

 

[shrivallabha]

You can try something like below and see if it works for you.

Dim c As Range
Set c = Cells.Find("*", , , , xlByColumns, xlPrevious).Offset(0, 1)
MsgBox "Empty Column is: " & c.Column

 

[Vishalunleashed]

Hi Shrivallabha,

Thank you for your response. I tried inputting the code suggested by you but pasting in the 1st row where data is already pasted based. Please find the below code for your reference.

Sub Macro_DB()






Sheets("TVI#").Select
Range("A1").Select

Sheets("Input").Select
Range("L5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("TVI#").Select
B = Worksheets("TVI#").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("TVI#").Cells(B + 1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Input").Select
Range("C4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("TVI#").Select
Dim c As Range
Set c = Cells.Find("*", , , , xlByColumns, xlPrevious).Offset(0, 1)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select

Sheets("Input").Select
Range("L2").Select
Selection.Copy
Range("L3:L12").Select
ActiveSheet.Paste
Columns("M:M").Select
Selection.EntireColumn.Hidden = True
Range("L6").Select
Application.CutCopyMode = False
Range("C3").Select
End Sub

 

[shrivallabha]

Please use CODE tags to wrap your code!

Add
c.Select
Before
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
and test.

It seems that you are using Macro Recorder so there's lot of select which you can get rid of!

 

[Vishalunleashed]

You mean like this:

c.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
:=False, Transpose:=True

It didnt worked?

 

[shrivallabha]

It will be easier to help if you could explain your end goal in plain English (instead of Macro Recorder code).

e.g. following code

Sheets("Input").Select
Range("C4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("TVI#").Select
Dim c As Range
Set c = Cells.Find("*", , , , xlByColumns, xlPrevious).Offset(0, 1)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

Can be written as (without using any Select statement)

Sheets("Input").Range(Sheets("Input").Range("C4"), Sheets("Input").Range("C4").End(xlDown)).Copy
Dim c As Range
Set c = Sheets("TVI#").Cells.Find("*", , , , xlByColumns, xlPrevious).Offset(0, 1)
Sheets("TVI#").Cells(1, c.Column).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False

 

[Vishalunleashed]

Thank you for your reply Shrivallabha, what I am trying to achieve is that in the attached macro the input file the user puts in the details of the devices that he wants to scan. The device id is unique and rest of the information like order number, customer, delivery date etc are repeated. The user once scan the devices through scanner can add it and it should get automatically uploaded in 3rd tab "TVI#" along with the transpose information in column c of "Input" sheet like order number, customer, delivery date, stock, supplier, etc.

I hope it gives you slightly better picture now.

 

[shrivallabha]

Where does the value for field "Tvilight" come from?

Following code shall copy data from range C4:C9 to the designated columns in TVI# sheet.

Sub Macro_DB()
    Sheets("Input").Range("C4:C9").Copy
    Dim lstRow As Long: lstRow = Sheets("TVI#").Range("A:G").Find("*", , , , xlByRows, xlPrevious).Offset(1, 0).Row
    Sheets("TVI#").Range("B" & lstRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Application.CutCopyMode = False
End Sub

 

[Vishalunleashed]

Thank you for prompt response Shrivallabha!

I tried using this code but is pasting at a different cell altogether. The value of "Tvilight" in TVI# sheet is what we want to add through this macro (upload button). This is a unique value for the number of devices which user will input. The rest of the information needs transposed into rows from C4:C9 of 1st tab and should be replicated as many times as number of devices. So if there are 3 devices it will have 3 different codes (Tvilight) and rest of the columns will have the information from C4:C9 (from 1st tab) 3 times.

 

[shrivallabha]

Thank you for prompt response Shrivallabha!

I tried using this code but is pasting at a different cell altogether. The value of "Tvilight" in TVI# sheet is what we want to add through this macro (upload button). This is a unique value for the number of devices which user will input. The rest of the information needs transposed into rows from C4:C9 of 1st tab and should be replicated as many times as number of devices. So if there are 3 devices it will have 3 different codes (Tvilight) and rest of the columns will have the information from C4:C9 (from 1st tab) 3 times.

What do you mean by different cell altogether?

As I see it, fields in B4:B9 on sheet "Input" correlate with B1:G1 on sheet "TVI#".

The code written above copies data from cells C4:C9 pastes it in the bottom most row on sheet "TVI#".

If this isn't desired behavior then you should manually prepare one example showing desired results from the input.

 

[Vishalunleashed]

To help you understand this I have uploaded the file with example. so in tab TVI# I have added the outcome and highlighted in pale yellow.

Basically it should take inputs from two places. One from column L (Scan your devices) from input tab and other from column C ranging from C4 : C9 Transposed.

 

[shrivallabha]

Okay. Test below macro and see.

Sub CopyDataToTVI()
Dim lstRow As Long, i As Long
For i = 1 To Sheets("Input").Range("C3").Value
    lstRow = Sheets("TVI#").Range("A:G").Find("*", , , , xlByRows, xlPrevious).Offset(1, 0).Row
    Sheets("TVI#").Range("A" & lstRow).Value = Sheets("Input").Range("L" & 4 + i).Value
    Sheets("Input").Range("C4:C9").Copy
    Sheets("TVI#").Range("B" & lstRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Next
Application.CutCopyMode = False
End Sub

One thing to note.
You should use "xlPasteValuesAndNumberFormats" in place of "xlPasteValues" so as to avoid date formatting related issues.

 

[Vishalunleashed]

Worked like a charm!

Thanks a lot

 

[shrivallabha]

Worked like a charm!

Thanks a lot

Good to know!