VBA - Copying and Pasting multiple ranges

[shakeregg]

Hey all,

I've got another slight query:

I have this code:

Sub Copy()
Application.ScreenUpdating = False
Dim wkbSource AsWorkbook, wsDest As Worksheet
Set wsDest =ThisWorkbook.Sheets("SMART Raw Data")
Dim lColumn AsLong
lColumn = 3
Const strPath AsString = "C:\mydocuments\audit\database"
ChDir strPath
strextension =Dir(strPath & "*.xlsm")
Do Whilestrextension <> ""
Ifstrextension <> ThisWorkbook.Name Then
SetwkbSource = Workbooks.Open(strPath & strextension)
With wkbSource
wsDest.Cells(5,lColumn).Resize(74).Value = .Sheets("AuditTool").Range("C5:C74").Value
lColumn = lColumn +1
.Closesavechanges:=False
End With
End If
strextension =Dir
On Error ResumeNext
Loop
Application.ScreenUpdating = True
End Sub

Mumps helped me out with it a while back and it works a dream!

I am now looking at extending it to copy and a number of ranges from this worksheet (Raw Data) and another worksheet (System Raw Data) both located from the "C:\mydocuments\audit\database" location.

Worksheet: Raw Data
C4:C500 (To be pasted to into worksheet: SMART RAW Data C4:C500)
D4:D500 (To be pasted to into worksheet: SMART RAW Data D4:D500)
F4:F500 (To be pasted to into worksheet: SMART RAW Data E4:E500)
H4:H500 (To be pasted to into worksheet: SMART RAW Data F4:F500)
J4:J500 (To be pasted to into worksheet: SMART RAW Data G4:G500)
L4:L500 (To be pasted to into worksheet: SMART RAW Data H4:H500)
M4:M500 (To be pasted to into worksheet: SMART RAW Data I4:I500)
N4:N500 (To be pasted to into worksheet: SMART RAW Data J4:J500)
O4:O500 (To be pasted to into worksheet SMART RAW Data K4:C500)

Worksheet: System Raw Data
C3:C499 (To be pasted to into worksheet SMART RAW Data P4:P500)
D3:D499 (To be pasted to into worksheet SMART RAW Data Q4:Q500)
E3:E499 (To be pasted to into worksheet SMART RAW Data R4:S500)
F3:F499 (To be pasted to into worksheet SMART RAW Data S4:S500).

Hopefully this makes sense!!


<strike></strike>

 

[jmacleary]

Hi there. If the ranges are fixed, just record a macro doing what you want and then either include the result at the end of your existing code (after the loop), or call it from the end of your existing code.