VBA code to copy, paste and transpose every 5th row from multiple worksheets

Hello - I have a range of values in one tab called "Debt" and I want to tranpose the data into rows starting in cell C2 on my "Statatest" tab. However, I would like to paste the data on every fifth row. I cannot figure out how to do this my current code only pastes and transposes to one row. In all, I have five worksheets with data I want to copy and transpose to the Statatest tab. Any thoughts are greatly appreciated?

As you can see from the code I am a VBA novice...

Here is my code:

Sub test()
Sheets("Statatest").Select
Columns("C:C").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Range("C1").Select
Sheets("Data").Select
Sheets("Statatest").Select
Sheets("Statatest").Move After:=Sheets(5)
Sheets("Productivity").Select
Range("B6:B1000").Select
Range("B6").Activate
Selection.Copy
Sheets("Statatest").Select
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, transpose:=True
Sheets("Debt").Select
Range("AE10").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Statatest").Select
Sheets("Statatest").Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, transpose:=

End Sub

What ranges do you need to transpose on each sheet. From your code it looks like you are doing B6:B1000 on the productivity sheet and AE10 to "the end" on the Debt sheet. What about the other sheets. Please clarify what ranges you need copied on what sheet.

The productivity sheet is the dates used for the time series. The data I need to copy and transpose from the other sheets also begins on the AE10 cell and includes to the end (down and to the right). I can email you a screen shot if needed.

Any thoughts? The most important part of the loop is to paste and transpose the data every fifth row. Any help is greatly appreciated.

Please Login or Register  to view this content.

What ranges do you need to transpose on each sheet?

An example answer would be;

Sheet "Productivity" Range B6:B1000
Sheet "Debt" Range AE10 to the last row, and every column to the right of column AE


Pasting on every 5th row is easy, i just want to make sure I know which ranges you want copied.

The data will update so I hope to have a dynamic code. See below. Thanks!

Sheet "Productivity" B6 --> all down until data ends (this is for the dates, which begin in C1 in the destination sheet)
Sheet "Debt" AE10 all down and every column to the right of AE (all down and all to right) until data ends
Sheet "Productivity" C6 all down and every column to the right of AE (all down and all to right) until data ends
Sheet "Terms of Trade" AE10 all down and every column to the right of AE (all down and all to right) until data ends
Sheet "REER" AE10 all down and every column to the right of AE (all down and all to right) until data ends
Sheet "FX" AE10 all down and every column to the right of AE (all down and all to right) until data ends

Essentially, I am shaping my data to upload into Stata in a panel format. On the destinattion sheet Cell A1 is my label for country and B1 is the variable name. C2 is where I transpose the dates. My First country is Australia so A2 to A6 are all Australia. B2 to B6 are debt, productivity, terms of trade REER and FX from each of the worksheets. I need to past every fifrth row since Canada debt is C7.

Sorry C1 is where I transpose the dates. The first variable is transposed into C2, which is Australia debt from sheet "Debt"

Canada debt on the sheet "Debt" will be transposed in C7 and euro zone debt in C12. I prefer to have the loop run through the data set (5 worksheets) and copy and transpose into my upload sheet (Statatest).

I hope this is clear. Thanks again.

Ok i think i got it. Gonna take me a little bit to code and test.

Great, thanks. It copies and transposes the dates from Productivity but none other data is copied and pasted into the Statatest sheet.

yeah that my fault. i realized a small error in the code, try this:

Please Login or Register  to view this content.

Does the set ws1 = sheets need to include the other sheets - Debt, Terms of Trade, REER and FX?

Originally Posted by cog38

Does the set ws1 = sheets need to include the other sheets - Debt, Terms of Trade, REER and FX?

No, the other ones should be covererd under the Case Else, since it is all the same actions in each of those sheets. However, I put it into a Select/Case format so in the future if you wanted to make special accomidation for a sheet you could easily specify it. Let me know if the above amended code works.

I see thanks. The code works but I not sure what data it is pullling in. My data are based on links from Bloomberg and the data pasted from the code leaves an error. Would you prefer I emailed you a copy of the sheet?

I cannot post to the website due to restrictions.

Furthermore, here is the sequence of the loop. Copy and paste from "Debt" AE10 (down and to the right) in this case to AS45 and transpose in Statatest C2 in every five rows until C72. Next, Copy and paste from "Productivity" C6 (down and to the right) in this case to Q48 and transpose in Statatest C3 in every five rows until C73. Copy and paste from "Terms of Trade" AE10 (down and to the right) in this case to AS45 and transpose in Statatest C4 in every five rows until C74. Same loop for sheets "REER" and "FX" which are pasted in C5 and C6 and finish at C76. Does that help?

Make an example workbook and as clearly as possible show (in the workbook) what you want to go where.

attached is the sheet.

Statatest is the destination sheet. In this tab I need the dates (from productivity) to be pasted in C1. Debt contains the debt variable for each country. As you can see each "variable" on statalist relates to a "sheet" with the data for each country. Barrring Productivity the data points I want to copy and paste into Stattest begins AE10. I do not plan to copy the data from CAB. I hope this helps.

There is no "Statatest" sheet on your example workbook. Make a sheet called Statatest, copy columns AB from "Sheet6" (so it looks nice) and run this code. It matches what you had on sheet 6.

Please Login or Register  to view this content.

Great, thanks! This works perfectly. My apologies but I sent an older version of the sheet. Will this code work for the sheets that I need to collect data beginning in Cell AS35 instead of B10. For instance, the Gross Government Debt tab has the necessary data in AS10 instead of B10. Will this code still work with that in mind? I will play with the sheet tomorrow. Can I follow up with any further questions? Thanks again.

Here is the updated Excel sheet. I tried to adjust the code with some of the changes I made from the prior sheet to no avail. Can you please update the code you posted? Your help has been greatly appreciated.

It is hard for me not to be snarky in this situation but here you go:

Please Login or Register  to view this content.

I do apologize for any inconvenience. As I mentioned, you have been a great help. I have one final question. I think the macro is pasting incorrect data points since I am getting #ref errors for most of the cells. I think it has to do with my API links to Bloomberg. How can I change the code so for sheets Debt, Terms of Trade, REER and FX (Not Productivty) to grab all the data down and to the right of cell AE10 instead of B10?

This shows the error and I will also attach a screen shot.

excel_screen.jpg

This shows the "debt" sheet. How can I adjust the code to copy and paste the data that begins in cell AE10 for Debt, Terms of trade, REER and FX?

That worked perfectly! All set. And thank you for not being snarky. This is extremely helpful...

hi - I have a follow up about this code. can I reply in this message thread?

I can't remember what I had for dinner last night, let alone what was going on in 2013 . What are you wanting to know?