How to Run Macro on specific sheet?

[Rookie79]

1st off, this is my first attempt at a macro. I copied someone posted here and tweaked it for hours till it did what I wanted it to do. I was so happy! Now, I am wanting to use the macro on one sheet and another macro in the 2nd sheet. Sheet one is named WTD, sheet 2 is named YTD. My code is rather long but I can post it if needed. I am sure I am overlooking something simple but each time I run the macros, they both run on WTD tab.

What I am doing is taking the info in 3 columns on each of my tabs and compiling it into one sheet. For WTD it is going to be coulmns G, H, I and for YTD it is going to be coulmns P, Q, R. I have one macro that pulls the WTD coulmns and one to pull the YTD columns. No matter what I try, both marcos will only run on the first tab, "WTD"

Any help would be greatly appreciated!

 

[hiker95]

Rookie79,

Welcome to the MrExcel forum.

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, mark the workbook for sharing, and provide us with a link to your workbook.

If posting VBA code, please use Code Tags, see below in my Signature block: If posting VBA code, please use Code Tags - like this

 

[Rookie79]

Sorry! I am using Window XP and Excel 2003.
Here is the begining and end of my code. I removed all the middle. Hope thsi format is ok.

Public Sub Log()
Dim a
Dim Total As Integer
Dim MyArray(109) As Variant
a = 1
Total = Worksheets.Count
If Total > 1 Then
For a = a To Total
Sheets(a).Activate
MyArray(1) = Range("G7").Value
MyArray(2) = Range("H7").Value
MyArray(3) = Range("I7").Value
MyArray(4) = Range("G8").Value
MyArray(5) = Range("H8").Value
MyArray(6) = Range("I8").Value
Sheets(1).Activate

If Range("B3") = "" Then
Range("B3").Activate
Else
Range("B15").End(xlUp).Offset(1, 0).Activate
End If
ActiveCell.Value = MyArray(1)
ActiveCell.Offset(0, 1).Value = MyArray(2)
ActiveCell.Offset(0, 2).Value = MyArray(3)
ActiveCell.Offset(0, 3).Value = MyArray(4)
ActiveCell.Offset(0, 4).Value = MyArray(5)
ActiveCell.Offset(0, 5).Value = MyArray(6)

Next a
End If
End Sub

 

[hiker95]

Rookie79,

You will generally get much more help (and faster) in this forum if you can post your small samples (sensitive data scrubbed/removed/changed) (what you have and what you expect to achieve) directly in the forum.


To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, mark the workbook for sharing, and provide us with a link to your workbook.

If posting VBA code, please use Code Tags, see below in my Signature block: If posting VBA code, please use Code Tags - like this

 

[Rookie79]

You will have to excuse me. I have never posted before. I am not able to upload any content. My basic question is what part of a macro tells it what sheet to perform on and how can I change it? My macro is posted above.

Thanks in advance!

 

[MPW]

Try including the sheet name along with your range.

Sheets("Sheet1").Range("G7").Value

 

[Rookie79]

Where do I put that? I tried replacing
Sheets(b).Activate

with is and I get an error.

 

[MPW]

Is the name of the sheet b? If it is, put quote marks around it.

 

[Rookie79]

No. The sheet names are WTD and YTD. WTD runs just fine but when using the macro on YTD, it puts the information back on the WTD tab. Something in my code is telling it to use the first sheet and I have changed and messed with everything I can think of to get it to run on the 2nd sheet "YTD" and am having no luck.

 

[MPW]

OK,

Try this test in a new workbook and see if it helps you understand it any better.
1. Type something in Range"A1").
2. Add a 2nd sheet.
3. Run this code.

Sub TestCode()
Sheets("Sheet2").Range("A1") = Sheets("Sheet1").Range("A1")
End Sub

The Range("A1") on your second sheet should now = Range("A1") on your first sheet.

Did this help you understand the process?