How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask The-PC-Guy Your Own Question
The-PC-Guy
The-PC-Guy, Software Engineer
Category: Programming
Satisfied Customers: 1936
Experience:  Extensive Knowledge in PHP, MYSQL, CSS & Javascript
62934938
Type Your Programming Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

How do I alter this macro to paste data in columns (looping

Customer Question

How do I alter this macro to paste data in columns (looping columns instead of rows)? This is for the paste function only.
Sub ConsolidateDta()
Dim i As Integer
Dim fil As String
Dim Col As String
Dim cpy As String
Dim ws As Worksheet
Dim twb As Workbook
Set ws = Sheet1 ' List sheet
Application.DisplayAlerts = False
Set twb = ThisWorkbook
On Error GoTo Err 'This is just in case a muppet mistypes a path or file name.
For i = 2 To ws.Range("B65536").End(xlUp).Row 'Sheet1 is MasterSheet
fil = ws.Range("C" & i) & ws.Range("B" & i) 'File Location plus XL name
cpy = ws.Range("D" & i) & ":" & ws.Range("E" & i) 'Copy Range
Col = Left(ws.Range("B" & i), 1) 'Col to paste to
Workbooks.Open fil, 0, 1 'Open Read Only
Range(cpy).Copy
twb.Sheets(ws.Range("F" & i).Value).Cells(Rows.Count, Col).End(xlUp)(2).PasteSpecial 12 'Vals only
ActiveWorkbook.Close False 'Close no save
Next i
Application.DisplayAlerts = False
Exit Sub
Err: 'Mup Mup
MsgBox "The file " & ws.Range("b" & i) & " is missing. Operation incomplete."
End Sub
Submitted: 10 months ago.
Category: Programming
Expert:  The-PC-Guy replied 10 months ago.

what do you mean loop through columns instead of rows for paste only?:

Customer: replied 10 months ago.
the VBA is correctly reading from a list of file names and file ranges to be copied. When it pastes the copied ranges it is counting rows and pasting directly under the last entry. I need the VBA to paste each copied range into the next column starting at Row 1 of each column. Each copied range starts in row 1 and is pasted into its own separate column.
Expert:  The-PC-Guy replied 10 months ago.

well you would loop through columns with something like this.

for i = 1 to 10 'whatever

cells(1,i).PasteSpecial

next i

so instead of using range to paste you just use cells where 1 is the row, and i is the column increment.

let me know if you need something more specific.

---------------------------------------------------------------------------------------------------------------

let me know if you have any questions, problems, or concerns

PLEASE DON'T FORGET TO RATE SO I AM PAID FOR MY TIME

IT WILL NOT COST ANYTHING ADDITIONAL BEYOND THE VALUE OF YOUR QUESTION

TO RATE, CLICK THE STARS AT THE TOP OF YOUR SCREEN

Do not rate negatively, instead continue the conversation with me so I can address any of your concerns

---------------------------------------------------------------------------------------------------------------

if you have any questions in the future you can visit my profile

http://www.justanswer.com/computer/expert-1expert-2rulethemall/

and ask a question right in my box,

also you can put "PC Guy only" in your question title if you want to get to me.

Customer: replied 10 months ago.
Can you be more specific please?
Expert:  The-PC-Guy replied 10 months ago.

sure what exactly are you trying to copy by looping through the column.

is it this line that you are referring to?

twb.Sheets(ws.Range("F" & i).Value).Cells(Rows.Count, Col).End(xlUp)(2).PasteSpecial 12 'Vals only

and is the range you are copying is it a row or a column?

Customer: replied 10 months ago.
I am copying a column. I have attached a view of list from Sheet1 of Workbook that includes their ranges. Please note destination ranges are also including in this list and could be used in the macro.
Expert:  The-PC-Guy replied 10 months ago.

ok, based on that image you are pulling the values directly from the sheet, then it looks to me like it should already loop through the columns, as you have A1 B1 C1 ect.. or am I missing something?

Customer: replied 10 months ago.
It doesn't loop through A1 B1 C1. Instead it copies down column i.
Expert:  The-PC-Guy replied 10 months ago.

can you wait until monday. It is getting late where I am. If this is urgent I will open to others.

Customer: replied 10 months ago.
It is urgent
Customer: replied 10 months ago.
Unless you attend to immediately provide your online assistance, please refund all money now!
Customer: replied 10 months ago.
Do you intend to provide the programming advice?
Expert:  The-PC-Guy replied 10 months ago.

as requested the question was opened to all experts, but it appears nobody else replied.

Are you still looking for help?

Customer: replied 10 months ago.
Someone replied only to charge me $39. Can you answer this question or do you want to give me a refund?
Expert:  The-PC-Guy replied 10 months ago.

i don't think anyone replied, it seams like you don't want an answer and are more concerned about a refund. So it would be best for me to opt out at this point.

to get a refund contact the sites customer service.

Expert:  The-PC-Guy replied 10 months ago.

also, I am not sure if you opened a second question or not, but since I did not provide answer yet it should not have been chraged, you can take that up with customer service when you talk to them

Related Programming Questions