VBA to Paste / Values / Transpose Multiple Workbooks

Question

My apologies, I am new to VBA as I am sure you have guessed. No previous answers to similar questions have been able to fix my problem.

I have a large number of Excel workbooks, each workbook has multiple worksheets in them all with different names and desired information in different ranges.

In an effort to consolidate and essentially make a manually updated dashboard type spreadsheet containing all the latest information I have been copying and pasting data into a "master sheet." Different workbooks (jobs) have different ranges on this one as well. I have been using the paste special, values, transpose function.

I am trying to come up with a macro paste/ values/ transpose AFTER I select and copied the desired information.

I have tried multiple suggestions from previous questions but none of them seem to work for me.

Sub paste_values()

    wbkCurrent.Activate
    Range("Selection").Select
    Selection.Copy
    Windows("job cost summary project.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
End Sub

&

Sub TransposePaste()
With ActiveCell
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End With
End Sub

I hoping for a macro that will be a one click solution to paste/transpose the data I have copied in another workbook.

Thank you so much for the response @JaraExcel I appreciate it!! I am still running into some issues, specifically "Run-time error '9': Subscript out of range" I am running the following: [link] italic bold `Sub Pasteinfo() Dim WS As Worksheet Set WS = ThisWorkbook.Worksheets("Job Cost Summary") WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Transpose:=True End Sub' — BigN'Husky

JaraExcel JaraExcel · Accepted Answer · 2019-04-17T21:30:51

Hi Basicaly I understood that you want to manually copy, but you want to paste the info with a macro.

If that´s the case, I would suggest to do the following:

in job cost summary project.xlsm write the following code:

Sub Pasteinfo dim WS as worksheet set WS = thisworkbook.worksheets("Sheet") ws.cells(rows.count,1).end(xlup).offset(1,0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats , Transpose:=True End sub
Assign a short cut to your macro by going> Developer > Macros > Select your macro > Options (and assign a shortcut)

With these steps you will be able to paste the info in your workbook after you manually copied the info.

VBA to Paste / Values / Transpose Multiple Workbooks

1 Answers