VBA for Copy and Paste between 2 workbooks

[VBAuser41]

Hello. I am trying to create code that copy and pastes information from sheet 1 (the whole sheet) of an xlsx file, we'll call it "Workbook 1", located on a users desktop (could be any user, but will always be on their desktop), into sheet 2 of a different xlsx file, called "Workbook 2". The pasted data would need to be Transposed as well. No other restrictions. How would I do this?

Thank you.

 

[mrshl9898]

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Assuming you're running the code out of Workbook2.

</code>Sub ocp()

Dim thiswb As Workbook
Dim otherwb As Workbook

Set thiswb = ActiveWorkbook

userlocation = CreateObject("WScript.Shell").specialfolders("Desktop")


Workbooks.Open (userlocation & "\Book1.xlsx")
Set otherwb = ActiveWorkbook
otherwb.Sheets("Sheet1").Copy After:=thiswb.Sheets(Sheets.Count)
otherwb.Close


End Sub


<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">

</code>

 

[VBAuser41]

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Assuming you're running the code out of Workbook2.

</code>Sub ocp()

Dim thiswb As Workbook
Dim otherwb As Workbook

Set thiswb = ActiveWorkbook

userlocation = CreateObject("WScript.Shell").specialfolders("Desktop")


Workbooks.Open (userlocation & "\Book1.xlsx")
Set otherwb = ActiveWorkbook
otherwb.Sheets("Sheet1").Copy After:=thiswb.Sheets(Sheets.Count)
otherwb.Close


End Sub


<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">

</code>

Thanks for the reply. This almost gets me there. This adds a new tab to book 2 and it does not transpose the pasted data. sheet 1 in book 2 is reading sheet 2, so adding a new tab is not an option. Can you adjust so it takes those things into consideration? Thanks!

 

[mrshl9898]

I'm a bit lost here, you want to copy the sheet and paste over an existing one?.

Try adjusting this to work this into it

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">thiswb.Worksheets("Sheet1").Range("A1:A5").Copy
otherwb.Worksheets("Sheet1").Range("A1").PasteSpecial Transpose:=True</code>

 

[VBAuser41]

Sorry, I realized you can't transpose by copying the entire sheet. So I Need to copy from A1 in book 1 down and over as far as there are contents in the cells. Similar to when you do ctrl+shift down arrow/right arrow.

 

[mrshl9898]

The macro recorder is handy for working things like this out, in this case it's.

Range("A1").Select    
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

 

[VBAuser41]

Yep, got that far. I finally got it working using what you created and some tweaking. Thank you for the help!

 

[mrshl9898]

You're welcome, congrats!