Loop to tidy up my code?
 

Hi folks,

I've got a bit of code (see below) that looks up sheet "manacs figures" in
workbook A, copies a range, finds sheet X in workbook B and pastes the
values. It then goes back to the same sheet in workbook A and copies another
range (usually column next to it etc) and then pastes to sheet Y in workbook
B.

Loop to tidy up my code?
 

Forgot to paste the code in ... doh!! ..... see below for code.

I've cut out some of the copy/paste lines to save space but i've left enough
so you can get an idea of what i'm doing

Regards,

Craig

Sub test()

Application.ScreenUpdating = False

Dim wbs As Workbook
Dim wbt As Workbook
Dim tref As String

Set wbs = Workbooks("ManAcs Import.xls")
Set wbt = Workbooks("ManAcs Master.xls")

tref = "AJ"

wbs.Sheets("ManAcs Figures").Range("E4:E330").Copy
wbt.Sheets("ABFI").Range(tref & "115").PasteSpecial Paste:=xlPasteValues
wbs.Sheets("ManAcs Figures").Range("F4:F330").Copy
wbt.Sheets("ABOP").Range(tref & "115").PasteSpecial Paste:=xlPasteValues
wbs.Sheets("ManAcs Figures").Range("G4:G330").Copy
wbt.Sheets("ABTE").Range(tref & "115").PasteSpecial Paste:=xlPasteValues
wbs.Sheets("ManAcs Figures").Range("H4:H330").Copy
wbt.Sheets("ABNN").Range(tref & "115").PasteSpecial Paste:=xlPasteValues

wbs.Sheets("ManAcs Figures").Range("J4:J330").Copy
wbt.Sheets("EDCC").Range(tref & "115").PasteSpecial Paste:=xlPasteValues
wbs.Sheets("ManAcs Figures").Range("K4:K330").Copy
wbt.Sheets("EDCO").Range(tref & "115").PasteSpecial Paste:=xlPasteValues
wbs.Sheets("ManAcs Figures").Range("L4:L330").Copy
wbt.Sheets("EDFI").Range(tref & "115").PasteSpecial Paste:=xlPasteValues



Application.CutCopyMode = False


Application.ScreenUpdating = True

End Sub









"Craig Handley" wrote:

Hi folks,

I've got a bit of code (see below) that looks up sheet "manacs figures" in
workbook A, copies a range, finds sheet X in workbook B and pastes the
values. It then goes back to the same sheet in workbook A and copies another
range (usually column next to it etc) and then pastes to sheet Y in workbook
B.

The rows being copied each time are constant (rows 4 to 330).

The paste destination is always row 115 (i change the column each month by
changing the column ref near the start of the code)

The value in row 2 of each column in my source workbook is the name of the
sheet in the destination sheet. i.e. E2 = "ABFI" ... i want to copy rows from
E4:E330 to AJ115 in sheet "ABFI".

As you can see i've got the long version of the code working where i specify
each column to copy from and which sheet to post to but i'm sure there must
be a way to shorten this using some form of loop or do while command and also
use the data to identify the destination sheet.

Hopefully all that makes sense and thanks in advance for any help/pointers
you can give.

Loop to tidy up my code?
 

It would be nicer if you had column i

Sub test()

Application.ScreenUpdating = False

Dim wbs As Workbook
Dim wbt As Workbook
Dim tref As String
Destinations = Array("ABFI", "ABOP", "ABTE", "ABNN", "", "EDCC", "EDCO",
"EDFI")

Set wbs = Workbooks("ManAcs Import.xls")
Set wbt = Workbooks("ManAcs Master.xls")

tref = "AJ"
For i = 0 To UBound(Destinations)
If i < 4 Then 'skip column i

wbs.Sheets("ManAcs Figures").Range("E4:E330").Offset(0, i).Copy
wbt.Sheets(Destinations(i)).Range(tref & "115").PasteSpecial
Paste:=xlPasteValues
End If
Next i

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


"Craig Handley" wrote:

Forgot to paste the code in ... doh!! ..... see below for code.

I've cut out some of the copy/paste lines to save space but i've left enough
so you can get an idea of what i'm doing

Regards,

Craig

Sub test()

Application.ScreenUpdating = False

Dim wbs As Workbook
Dim wbt As Workbook
Dim tref As String

Set wbs = Workbooks("ManAcs Import.xls")
Set wbt = Workbooks("ManAcs Master.xls")

tref = "AJ"

wbs.Sheets("ManAcs Figures").Range("E4:E330").Copy
wbt.Sheets("ABFI").Range(tref & "115").PasteSpecial Paste:=xlPasteValues
wbs.Sheets("ManAcs Figures").Range("F4:F330").Copy
wbt.Sheets("ABOP").Range(tref & "115").PasteSpecial Paste:=xlPasteValues
wbs.Sheets("ManAcs Figures").Range("G4:G330").Copy
wbt.Sheets("ABTE").Range(tref & "115").PasteSpecial Paste:=xlPasteValues
wbs.Sheets("ManAcs Figures").Range("H4:H330").Copy
wbt.Sheets("ABNN").Range(tref & "115").PasteSpecial Paste:=xlPasteValues

wbs.Sheets("ManAcs Figures").Range("J4:J330").Copy
wbt.Sheets("EDCC").Range(tref & "115").PasteSpecial Paste:=xlPasteValues
wbs.Sheets("ManAcs Figures").Range("K4:K330").Copy
wbt.Sheets("EDCO").Range(tref & "115").PasteSpecial Paste:=xlPasteValues
wbs.Sheets("ManAcs Figures").Range("L4:L330").Copy
wbt.Sheets("EDFI").Range(tref & "115").PasteSpecial Paste:=xlPasteValues



Application.CutCopyMode = False


Application.ScreenUpdating = True

End Sub









"Craig Handley" wrote:

Hi folks,

I've got a bit of code (see below) that looks up sheet "manacs figures" in
workbook A, copies a range, finds sheet X in workbook B and pastes the
values. It then goes back to the same sheet in workbook A and copies another
range (usually column next to it etc) and then pastes to sheet Y in workbook
B.

The rows being copied each time are constant (rows 4 to 330).

The paste destination is always row 115 (i change the column each month by
changing the column ref near the start of the code)

The value in row 2 of each column in my source workbook is the name of the
sheet in the destination sheet. i.e. E2 = "ABFI" ... i want to copy rows from
E4:E330 to AJ115 in sheet "ABFI".

As you can see i've got the long version of the code working where i specify
each column to copy from and which sheet to post to but i'm sure there must
be a way to shorten this using some form of loop or do while command and also
use the data to identify the destination sheet.

Hopefully all that makes sense and thanks in advance for any help/pointers
you can give.