If macros

[robh0409]

Hi

I am trying to create a macro that will work by looking at a cell to determine the month, then copy the information into the relevant month on another workbook. This is what I have so far:

[pre]

Sub transfer()

' transferring Macro

If ("E3") = "Jan" Then
Windows("Leatherhead_Greenford MASTER_TEST.xlsx").Activate
Sheets("Jan").Select
Windows("ProjectBook1.xlsm").Activate
Sheets("Sheet3").Select
Range("A3:G125").Select
Selection.copy
Windows("Leatherhead_Greenford MASTER_TEST.xlsx").Activate
Sheets("Jan").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select

ElseIf ("E3") = "Feb" Then
Windows("Leatherhead_Greenford MASTER_TEST.xlsx").Activate
Sheets("Feb").Select
Windows("ProjectBook1.xlsm").Activate
Sheets("Sheet3").Select
Range("A3:G125").Select
Selection.copy
Windows("Leatherhead_Greenford MASTER_TEST.xlsx").Activate
Sheets("Feb").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select

etc for each month

End Sub

[/pre]

It works in regard that there are no errors, but there is the slight problem that it isn't copying the information over to the other workbook


Any help would be greatly appreciated


Thanks

Rob

 

[NARAYANK991]

Hi Rob ,


Try this :

[pre]

Sub transfer()

Dim From_Book As Workbook, To_Book As Workbook
Dim From_Sheet As Worksheet, To_Sheet As Worksheet
Dim sheet_name As String

Set From_Book = ThisWorkbook
Set From_Sheet = From_Book.Worksheets("Sheet3")

sheet_name = From_Book.Worksheets("Sheet2").Range("E3").Value

Set To_Book = Workbooks("Leatherhead_Greenford MASTER_TEST.xlsx")

To_Book.Activate
Set To_Sheet = ActiveWorkbook.Worksheets(sheet_name)

From_Book.Activate
From_Sheet.Select
Range("A3:G125").Copy

To_Book.Activate
To_Sheet.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub

[/pre]
Note that there is no error checking to see if E3 contains a valid sheet name.


Narayan

 

[robh0409]

Thanks for your reply, I may be being very stupid with this but when I run the code, it comes up with this error

Run time error '9'

Script out of range


Any idea on how to resolve this?


Thanks

Rob

 

[NARAYANK991]

Hi Rob ,


Can you mention the following ?


1. Where has this macro been copied i.e. from which workbook is this macro being run ?


2. Which statement gives this error ?


Narayan

 

[robh0409]

Hi,


this is being run in the ProjectBook1.xlsm workbook trying to copy into the Leatherhead_Greenford MASTER_TEST.xlsx workbook


the statement which gives the error is

Set To_Sheet = ActiveWorkbook.Worksheets(sheet_name)

Thanks

Rob

 

[NARAYANK991]

Hi Rob ,


Modify the following statement :


sheet_name = From_Book.Worksheets("Sheet2").Range("E3").Value


according to which sheet in your workbook ProjectBook1.xlsm contains Jan , Feb , Mar etc., in E3.


Replace Sheet2 in the above statement with the correct tab name.


Narayan

 

[robh0409]

Thank you, now works perfectly!


Rob