Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Josefina Stanley   on Nov 27 In MS Office Category.

  
Question Answered By: Hubert Taylor   on Nov 27

But can I add the part  you wrote to the same macro?

I tried it like this:

ActiveSheet.Range("A2:ad13", Range("A2:ad13").End(xlToRight)).Copy _
Destination:=Worksheets("DMH").Range("A2")
Dim rngCell As Range

With Worksheets("DMH")

For Each rngCell In .Range("d2:d15")
'Copy to January
If rngCell.Value = 1 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A18")
'Copy to February
If rngCell.Value = 2 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A33")
'Copy to March
If rngCell.Value = 3 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A48")
'Copy to April
If rngCell.Value = 4 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A63")
'Copy to May
If rngCell.Value = 5 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A63")
'Copy to June
If rngCell.Value = 6 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A93")
'Copy to July
If rngCell.Value = 7 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A108")
'Copy to August
If rngCell.Value = 8 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A123")
'Copy to September
If rngCell.Value = 9 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A138")
'Copy to October
If rngCell.Value = 10 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A153")
'Copy to November
If rngCell.Value = 11 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A168")
'Copy to December
If rngCell.Value = 12 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A183")

Next rngCell

End With

But get an error:
On this..

.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A123")
I'm assuming it's because I'm on the ALLDATA sheet.
Becuase if I rum it from DMH sheet  it work fine.
I assume I have to name the sheet it's going to?

Share: 

 

This Question has 5 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on on If statement in a macro ? Or get search suggestion and latest updates.


Tagged: