Copy data to next available column in different workbook

[13373]

I have tried to piece something together to work with information from different forums, but I cannot get something to work. Please look at my code and advise what changes need to be made to make this work.

Thank you for your time.


Sub Macro1()

Workbooks.Open Filename:="C:\Filepath\Mastercard Master Tracking.xlsm"
Sheets("Raw Data").Active
Application.DisplayAlerts = False

Dim wbTarget As Workbook, wbSource As Workbook, wr As Worksheet, r As Long
Set wbTarget = Workbook("Mastercard Master Tracking"): wbTarget = Workbook("Mastercard Tracking"): wr = Worksheet("Raw Data")

r = wbTarget.wr.Range("B1").End(xlToRight).Column
wbTarget.wr.Range(r & 1) = wbSource.Range("B2")
wbTarget.wr.Range(r & 2) = Format(Now(), "MM/DD/YYYY")
wbTarget.wr.Range(r & 3) = Format(Now(), "hh:mm")
wbTarget.wr.Range(r & 5) = wbSource.Range("C8")
wbTarget.wr.Range(r & 6) = wbSource.Range("C9")
wbTarget.wr.Range(r & 7) = wbSource.Range("C10")
wbTarget.wr.Range(r & 8) = wbSource.Range("C11")
wbTarget.wr.Range(r & 9) = wbSource.Range("C12")
wbTarget.wr.Range(r & 10) = wbSource.Range("C13")
wbTarget.wr.Range(r & 11) = wbSource.Range("C14")
wbTarget.wr.Range(r & 12) = wbSource.Range("C15")
wbTarget.wr.Range(r & 13) = wbSource.Range("C16")
wbTarget.wr.Range(r & 14) = wbSource.Range("C17")
wbTarget.wr.Range(r & 15) = wbSource.Range("C18")
wbTarget.wr.Range(r & 16) = wbSource.Range("C19")
wbTarget.wr.Range(r & 17) = wbSource.Range("C20")
wbTarget.wr.Range(r & 18) = wbSource.Range("C21")
wbTarget.wr.Range(r & 19) = wbSource.Range("C22")
wbTarget.wr.Range(r & 20) = wbSource.Range("C23")
wbTarget.wr.Range(r & 21) = wbSource.Range("C24")
wbTarget.wr.Range(r & 22) = wbSource.Range("C25")
wbTarget.wr.Range(r & 23) = wbSource.Range("C26")
wbTarget.wr.Range(r & 24) = wbSource.Range("C27")
wbTarget.wr.Range(r & 25) = wbSource.Range("C28")
wbTarget.wr.Range(r & 26) = wbSource.Range("C29")
wbTarget.wr.Range(r & 27) = wbSource.Range("C30")
wbTarget.wr.Range(r & 29) = wbSource.Range("H8")
wbTarget.wr.Range(r & 30) = wbSource.Range("H9")
wbTarget.wr.Range(r & 31) = wbSource.Range("H10")
wbTarget.wr.Range(r & 32) = wbSource.Range("H11")
wbTarget.wr.Range(r & 33) = wbSource.Range("H12")
wbTarget.wr.Range(r & 34) = wbSource.Range("H13")
wbTarget.wr.Range(r & 35) = wbSource.Range("H14")
wbTarget.wr.Range(r & 36) = wbSource.Range("H15")
wbTarget.wr.Range(r & 37) = wbSource.Range("H16")
wbTarget.wr.Range(r & 38) = wbSource.Range("H17")
wbTarget.wr.Range(r & 39) = wbSource.Range("H18")
wbTarget.wr.Range(r & 40) = wbSource.Range("H19")
wbTarget.wr.Range(r & 41) = wbSource.Range("H20")
wbTarget.wr.Range(r & 42) = wbSource.Range("H21")
wbTarget.wr.Range(r & 43) = wbSource.Range("H22")
wbTarget.wr.Range(r & 44) = wbSource.Range("H23")

Windows("Mastercard Master Tracking.xlsm").Save
Windows("Mastercard Master Tracking.xlsm").Close
Application.DisplayAlerts = True

End Sub

[vamosj]

Considering the nature of your spreadsheet and that it would be difficult to upload without compromising identities, it would be extremely helpful if give us a little information on what problems are happening.

[13373]

Thank you for your reply.

When I attempt to execute the macro, nothing happens and I get a Compile Error: Sub or Function not defined.

[Pauleyb]

This catches my eye:
Set wbTarget = Workbook("Mastercard Master Tracking"): wbTarget = Workbook("Mastercard Tracking"): wr = Worksheet("Raw Data")
and
wbSource is not defined.

[13373]

Thank you Pauleyb

I have corrected it to the following, however I am still getting the same error.

wbSource = Workbook("Mastercard Tracking")

[Pauleyb]

There is quite a bit wrong, that I see. I recommend you get a book on VBA programming or visit some of the tutorial sites. Also, use the debugger to help determine where and why a failure is happening.
Your immediate problem is that it is worksheets and not worksheet. It may also be workbooks instead of workbook, but I didn't check. It would help to separate that line so that the debugger can help show you which part of that line is causing a problem.

[13373]

Thank you again for your help and suggestions. I was able to get it working with the following code. Not sure if there is a more efficient way, or if this will break down after 1000 columns, but it is working for now.

If you had any further suggestions on things that should be changed, any assistance is appreciated.

Please Login or Register  to view this content.