EXCEL VBA Code Streamline Help!!!

[yccyccycc]

I have created a macro to move data from one worksheet to another. I would like to streamline the codes in red since the range of the rows is the same but different columns. Not sure how to shorten it. Please help!

I would like to copy E3:E19 from workbook 1 to F12:F28 workbook2.
E31: E43 from workbook 1 to F46:60 Workbook2
..........................................................
H3:H19 from workbook 1 to I12:I28 workbook2.
H31:H43 from workbook 1 to I46:I60 Workbook2
...AND ETC.



Here is the macro:

Option Explicit

Sub StoreMonthly()
Dim destinationWorkBook As Workbook, destinationWorkSheet As Worksheet, fromWorkSheet As Worksheet, fromWorkBook As Workbook, LastRow As Long
Dim directory As String, fromFileName As String, destinationFileName As String
Dim ws As Worksheet, blnOpened As Boolean
directory = "C:\XXXX"
fromFileName = "From.xls"
destinationFileName = "To.xls" 'change name here

'This is a seperator
If WbOpen(destinationFileName) = True Then
Set destinationWorkBook = Workbooks(destinationFileName)
blnOpened = False
Else
If Right(directory, 1) <> Application.PathSeparator Then
directory = directory & Application.PathSeparator
End If
Set destinationWorkBook = Workbooks.Open(directory & destinationFileName)
blnOpened = True
End If
Set destinationWorkSheet = destinationWorkBook.Sheets("Tosheet") 'change destination sheet name here
'End seperator

'This is a seperator
If WbOpen(fromFileName) = True Then
Set fromWorkBook = Workbooks(fromFileName)
blnOpened = False
Else
If Right(directory, 1) <> Application.PathSeparator Then
directory = directory & Application.PathSeparator
End If
Set fromWorkBook = Workbooks.Open(directory & fromFileName)
blnOpened = True
End If
Set fromWorkSheet = fromWorkBook.Sheets("Fromsheet") 'change destination sheet name here

'End seperator

Call ToggleEvents(False)
'This is a seperator

destinationWorkSheet.Range("F12:F28").Value = fromWorkSheet.Range("E3:E19").Value
destinationWorkSheet.Range("F31:F43").Value = fromWorkSheet.Range("E20:E32").Value
destinationWorkSheet.Range("F46:F60").Value = fromWorkSheet.Range("E33:E47").Value
destinationWorkSheet.Range("H12:H28").Value = fromWorkSheet.Range("I3:I19").Value
destinationWorkSheet.Range("H31:H43").Value = fromWorkSheet.Range("I20:I32").Value
destinationWorkSheet.Range("H46:H60").Value = fromWorkSheet.Range("I33:I47").Value
If blnOpened Then
destinationWorkBook.Close SaveChanges:=True
fromWorkBook.Close SaveChanges:=True
End If
Call ToggleEvents(True)
End Sub

Sub ToggleEvents(blnState As Boolean)
With Application
.DisplayAlerts = blnState
.EnableEvents = blnState
.ScreenUpdating = blnState
If blnState Then .CutCopyMode = False
If blnState Then .StatusBar = False
End With
End Sub

Function WbOpen(wbName As String) As Boolean
On Error Resume Next
WbOpen = Len(Workbooks(wbName).Name)
End Function

 

[wigi]

It is not possible to streamline a lot here. How do you code it to go from E (5) to F (6), then from I (9) to H (8)?

In brackets I put the column number.

Not a logical pattern I'm afraid. The code will be more difficult to read afterwards.

Using the With... End With is a good advice though.

 

[yccyccycc]

Hello wigi,

can you please explain more about "Using the With... End With"?

I'm a newbie...very very very new to VBA....Thanks!

 

[wigi]

can you please explain more about "Using the With... End With"?

But you already used it in your code? :?:

With destinationWorkSheet
   .Range("F12:F28").Value = fromWorkSheet.Range("E3:E19").Value
   .Range("F31:F43").Value = fromWorkSheet.Range("E20:E32").Value
   .Range("F46:F60").Value = fromWorkSheet.Range("E33:E47").Value
   .Range("H12:H28").Value = fromWorkSheet.Range("I3:I19").Value
   .Range("H31:H43").Value = fromWorkSheet.Range("I20:I32").Value
   .Range("H46:H60").Value = fromWorkSheet.Range("I33:I47").Value
End With

 

[yccyccycc]

I see...thanks!