Transpose from 1 sheet to another

[Babynod]

Hi All,

i am looking to make a laptop register for work,

what i want is to past the laptop specs into a sheet and hit the cmdbutton. this will then transpose it from vertical to horizontal. i need it to enter into sheet2 on the next row down. at the moment i can only get it to overwrite the cell i defined, not the next empty one.

this is an example of the data i will be entering. i will be transposing the right column, not the headers on the left

OS Name	Microsoft Windows 11
Version	10.0.22000 Build 22000
Other OS Description	Not Available
OS Manufacturer	Microsoft Corporation
System Name	TULLAP008
System Manufacturer	HP
System Model	HP EliteBook 840 G5
System Type	x64-based PC
System SKU	3TU06PA#ABG
Processor	Intel(R) Core(TM) i5-8350U CPU @ 1.70GHz, 1896 Mhz, 4 Core(s), 8 Logical Processor(s)
BIOS Version/Date	HP Q78 Ver. 01.18.01, 24/11/2021
SMBIOS Version	3.1​
Embedded Controller Version	4.109​
BIOS Mode	UEFI
BaseBoard Manufacturer	HP
BaseBoard Product	83B2
BaseBoard Version	KBC Version 04.6D.00
Platform Role	Mobile
Secure Boot State	On
PCR7 Configuration	Elevation Required to View
Windows Directory	C:\WINDOWS
System Directory	C:\WINDOWS\system32
Boot Device	\Device\HarddiskVolume2
Locale	United States
Hardware Abstraction Layer	Version = "10.0.22000.1"
User Name	MAH\Mark
Time Zone	AUS Eastern Standard Time
Installed Physical Memory (RAM)	8.00 GB
Total Physical Memory	7.85 GB
Available Physical Memory	847 MB
Total Virtual Memory	17.3 GB
Available Virtual Memory	6.43 GB
Page File Space	9.50 GB
Page File	C:\pagefile.sys
Kernel DMA Protection	Off
Virtualization-based security	Not enabled
Device Encryption Support	Elevation Required to View
Hyper-V - VM Monitor Mode Extensions	Yes
Hyper-V - Second Level Address Translation Extensions	Yes
Hyper-V - Virtualization Enabled in Firmware	Yes
Hyper-V - Data Execution Protection	Yes

 

[My Aswer Is This]

To help we always need specific details.
You said:
specs into a sheet

What are the two sheet names

And you said:

"this is an example of the data i will be entering. i will be transposing the right column"

Right column??

We need column number like column G or column 7

 

[Alex Blakenburg]

Assuming you want to paste it starting in column A on sheet2, try this:

Sub LaptopRegisterTranspose()

    Dim srcSht As Worksheet, destSht As Worksheet
    Dim srcRng As Range, destRng As Range
    
    Set srcSht = Worksheets("Sheet1")
    Set destSht = Worksheets("Sheet2")
    With destSht
        Set destRng = .Range("A" & Rows.Count).End(xlUp).Offset(1)
    End With
    
    With srcSht
        Set srcRng = .Range("B1:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
    End With
    
    srcRng.Copy
    destRng.PasteSpecial Paste:=xlPasteValues, Transpose:=True
    
    Application.CutCopyMode = False

End Sub

 

[Babynod]

Assuming you want to paste it starting in column A on sheet2, try this:

Sub LaptopRegisterTranspose()

    Dim srcSht As Worksheet, destSht As Worksheet
    Dim srcRng As Range, destRng As Range
   
    Set srcSht = Worksheets("Sheet1")
    Set destSht = Worksheets("Sheet2")
    With destSht
        Set destRng = .Range("A" & Rows.Count).End(xlUp).Offset(1)
    End With
   
    With srcSht
        Set srcRng = .Range("B1:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
    End With
   
    srcRng.Copy
    destRng.PasteSpecial Paste:=xlPasteValues, Transpose:=True
   
    Application.CutCopyMode = False

End Sub

this works great, the only thing ill want to change is this adds it to the bottom of my table on sheet2, i want it to start from the top of the table

 

[Alex Blakenburg]

this works great, the only thing ill want to change is this adds it to the bottom of my table on sheet2, i want it to start from the top of the table

You're original text did say "the next empty one".

To put it in the first row we need more information.
• What row are your headings on in Sheet2 ?
• When you say your "table" is that an actual Excel Table and if so what is the name of the Table ?
• Is there anything to the right of the table, ie can we insert a full row or does the insertion need to stay within the table boundaries ?

 

[Babynod]

You're original text did say "the next empty one".

To put it in the first row we need more information.
• What row are your headings on in Sheet2 ?
• When you say your "table" is that an actual Excel Table and if so what is the name of the Table ?
• Is there anything to the right of the table, ie can we insert a full row or does the insertion need to stay within the table boundaries ?

Hi Alex,

i think it was taking the premade table (from format as table button) as a full cell and was inserting the data below that, i ended up deleting all the rows of the table except the first one and now its entering the data right.

sheet1 = Data Entry
sheet2 = Database
theres nothing to the right of the table

ive setup a custom sort on column F to sort A-Z which works now if i prompt it manually, but when i try to prompt it on worksheet_change but it bugs out

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

 

[Alex Blakenburg]

If we you are happy you have a fix for the original subject of this thread, you will need to start a new thread for the new question. It is a very different subject.

When you create the new thread you need to clarify what you are trying to do. Your text refers to a "Sort" but your code refers to a "Filter".
Also I can't imagine that you want it to run it everytime someone makes any kind of change to the spreadsheet.
I think you need to save your sort list somewhere on the spreadsheet and that it needs to be reapplied each time you run it but if you start a new thread others may be able to give you other options.

 

[Babynod]

If we you are happy you have a fix for the original subject of this thread, you will need to start a new thread for the new question. It is a very different subject.

When you create the new thread you need to clarify what you are trying to do. Your text refers to a "Sort" but your code refers to a "Filter".
Also I can't imagine that you want it to run it everytime someone makes any kind of change to the spreadsheet.
I think you need to save your sort list somewhere on the spreadsheet and that it needs to be reapplied each time you run it but if you start a new thread others may be able to give you other options.

ok thanks for the tips alex