Moving between sheets in a workbook

[JT_2111]

I have 4 sheets in my workbook (11NN, 11NB, 17NN and 17NB) and I need my macro to copy and paste data from a worksheet into a different corresponding worksheet. For example my data goes into 11NN then it gets copied and pasted into 11NB and the same for the two 17 tabs. So I need the macro to know that the data has gone into 11 vs 17 then copy and paste it into 11 and not 17. I am new to VBA and this is my first time making a macro so I hope the explanation isn't confusing. I know that I will need to incorporate variables but I am just not sure exactly how to make this work.

[RudiS]

Hi,

Welcome to the forum

Is it true to assume that the corresponding sheet (into which the data should be copied) is immediately to the right of the input sheet?

[JT_2111]

Yes it is immediately to the right. Not sure if it will matter or not but that sheet will be hidden.

[RudiS]

TX,

Hidden should not matter....
Would you trigger the macro directly after updating the ??NN sheet? In other words when should the macro run?, what data will the macro need to copy and where should the data to be pasted go on the ??NB sheet?

[JT_2111]

The macro is attached to a button on the NN sheet and will be clicked then the data will be populated from a saved file then copied over to the NB sheet. Both of the sheets are exactly the same so they will be pasted over to the same cells.

[RudiS]

Hi,

Try code like this for each of those worksheet buttons...

Sub CopyData()
Dim sAddr As String
Dim shN As Integer
    sAddr = ActiveSheet.UsedRange.Address
    shN = ActiveSheet.Index
    ActiveSheet.Range(sAddr).Copy
    Sheets(shN + 1).Range(sAddr).PasteSpecial
End Sub

[JT_2111]

Here is part of what I have in that macro. This section fixes some of the formating and highlighting for the sheet. I have bolded the section where I am having trouble. I only want to have to write this once and not put it into each button. So I need this to be able to see what sheet it is in, either 11 or 17, and put it into the 11NB or 17NB that it is linked to.


Range("C6:AI40").Select
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.ClearComments
Range("C6:E40,I6:K40,O6:Q40,U6:W40,AA6:AC40,AG6:AI40").Select
Range("AG6").Activate
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Range("C6:AI40").Select
Selection.Copy
Sheet34.Visible = True
Sheets("11 NB").Select
Range("C6").Select
ActiveSheet.Paste
Range("A1:B1").Select
Sheet34.Visible = xlSheetHidden
Sheets("11 NN").Select
Application.CutCopyMode = False
Range("A1:B1").Select

[RudiS]

Try this:
The red part should make this code universal...

    Range("C6:AI40").Select
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.ClearComments
    Range("C6:E40,I6:K40,O6:Q40,U6:W40,AA6:AC40,AG6:AI40").Select
    Range("AG6").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Range("C6:AI40").Select
    Selection.Copy
    Dim shN As Integer
    shN = ActiveSheet.Index
    Sheets(shN + 1).Range("C6").PasteSpecial
    Application.CutCopyMode = False
    Range("A1:B1").Select

[JT_2111]

So far this has worked! Thank you so much RudiS!

[RudiS]

Excellent.... Good to know