Go to sheet based on cell refference and copy/transpose values in a different sheet

M

MEP1984

New Member
Joined
Sep 29, 2015
Messages
10
Hi, all,
I have the following problem
I have a file, in which a new sheet is added every month with new information. My aim is to create a macro on a ceparate worksheet((named Macro) in this file, that goes into the sheet for the respective month, based on cell refference. For ex, if A2 = Jan16, go to sheet Jan16. Then it should filter the values that are not equal (for ex- the movement of the prices of articles through the year plus the current month). For that purpose I have added a new column in each sheet, comparing the values, so the Macro should go to this column (AO, refresh and filter the "false"(If the price is the same- no action. If not- copy the range of materials- visible cells only in cell range A4-T4 (and right untill we have values as each month there will be a new column) untill the bottom where we have values, and paste special in sheet Macro, values, transpose (the months to be vertical, the article numbers- horisontal.)
I have read a lot and tried to create a VBA code - simple macro copying my actions. But I receive an error message. The code works well when I simply go into a sheet and copy paste special the values. But when I try to update the code with refference to the cell A2, the macro doesnt work, and I receive an error message for the copy paste special/transpose part, not for the part of the cell refference.
Could you please advise what i should do? If additional information is needed- just let me know.
Thanks to all!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
MEP1984 said:
Hi, all,
I have the following problem
I have a file, in which a new sheet is added every month with new information. My aim is to create a macro on a ceparate worksheet((named Macro) in this file, that goes into the sheet for the respective month, based on cell refference. For ex, if A2 = Jan16, go to sheet Jan16. Then it should filter the values that are not equal (for ex- the movement of the prices of articles through the year plus the current month). For that purpose I have added a new column in each sheet, comparing the values, so the Macro should go to this column (AO, refresh and filter the "false"(If the price is the same- no action. If not- copy the range of materials- visible cells only in cell range A4-T4 (and right untill we have values as each month there will be a new column) untill the bottom where we have values, and paste special in sheet Macro, values, transpose (the months to be vertical, the article numbers- horisontal.)
I have read a lot and tried to create a VBA code - simple macro copying my actions. But I receive an error message. The code works well when I simply go into a sheet and copy paste special the values. But when I try to update the code with refference to the cell A2, the macro doesnt work, and I receive an error message for the copy paste special/transpose part, not for the part of the cell refference.
Could you please advise what i should do? If additional information is needed- just let me know.
Thanks to all!
Click to expand...

please post the code you have.

use code tags. Code tags are the words code (to start the code area) and /code (to end the code area). both words are contained inside square brackets [ ] .
 
Upvote 0
Rich (BB code): 
Sub Macro1()
'
' Macro1 Macro
'
'
    Range("E1:CA1068").Select
    Selection.ClearContents
    Selection.ClearContents
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("D4").Select
    Sheets("Macro").Range("A1").Select
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    Rows("4:4").Select
    Range("H4").Activate
    Selection.AutoFilter
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFilter
    ActiveCell.Offset(-1, 33).Range("A1").Select
    Range("AO6").Select
    ActiveCell.FormulaR1C1 = _
        "=AND((RC8=RC9),(RC9=RC10),(RC10=RC11),(RC11=RC12),(RC12=RC13),(RC13=RC14),(RC14=RC15),(RC15=RC16),(RC16=RC17),(RC17=RC18),(RC18=RC19))"
    Range("AO6").Select
    Selection.AutoFill Destination:=Range("AO6:AO390")
    Range("AO6:AO390").Select
    Range("AO6").Select
    Selection.AutoFill Destination:=Range("AO6:AO389"), Type:=xlFillDefault
    Range("AO6:AO389").Select
    ActiveWindow.ScrollRow = 360
    ActiveWindow.ScrollRow = 352
    ActiveWindow.ScrollRow = 266
    ActiveWindow.ScrollRow = 86
    ActiveWindow.ScrollRow = 5
    Rows("4:4").Select
    Range("J4").Activate
    Selection.AutoFilter
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFilter
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    Range("AO4").Select
    ActiveSheet.Range("$A$4:$AO$390").AutoFilter Field:=41, Criteria1:="FALSE"
    ActiveCell.Offset(0, -40).Range("A1").Select
    Range("A4:T208").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Macro").Select
    Range("F3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True
    Range("C7").Select
End Sub

The bolded part gives me an error. I tried to change it, and it was saved, so anyway now it is not correct, but I am posting it all.
 
Upvote 0
ok we are going to need to clean this up so we can see exactly what is going on. This will be helpful in two ways:

I. easier to read, debug and add on to the code when you need to
II. ensure that it is dynamic to your data so that the actions are always affecting the correct areas of data


So I have some questions based on your code.

1. Do we need the first block of code that remove borders, clear contents and removes shading?
2. what row does your data start on?
3. what column does your data start in?
4. what column does your data end in?
5. Other than the sheet Macro are there any other sheets you are working in?
6. I see three actions of import here. A formula that is entered and then copied down, a filter on false in column AO, and finally a copying of the filter cells to range F3 of the marco sheet. Am I missing any other important actions?
 
Upvote 0
Hi, below in bold are my replies:

1. Do we need the first block of code that remove borders, clear contents and removes shading? --> As I am planning to use sheet macro every month, this is to ensure that the old data is deleted and we start on a blank page. Any advise on optimising this will be helpful.
2. what row does your data start on? - In each sheet the data starts on row 4. Actual values are on row 6, there are blank rows through the rows down, which should not be taken into concideration. I mean, despite them, we need to have the whole data filtered later
3. what column does your data start in? - In each sheet, column A.
4. what column does your data end in? -Flexible, depending on the month. For November, Column S, as T is an important note. So in Dec it should end in T, and U will be the note.
5. Other than the sheet Macro are there any other sheets you are working in? - the sheets per months, in which I add column with comparison of the data ex. (=AND(($H6=$I6),($I6=$J6),($J6=$K6),($K6=$L6),($L6=$M6),($M6=$N6),($N6=$O6),($O6=$P6),($P6=$Q6),($Q6=$R6),($R6=$S6)) I only need information for the "false" result. Where the data matches, everything remains the same.
6. I see three actions of import here. A formula that is entered and then copied down (the formula in column AO), a filter on false in column AO (yes), and finally a copying of the filter cells to range F3 of the marco sheet (Range F3 is random. Could be E3,C2... as long as A and B are free). Am I missing any other important actions?- After copying, the data should be transposed in sheet macro with values only
 
Upvote 0
One more important note- the macro should choose the sheet based on the info in cell A2 - respectively Nov16, Dec16 etc.
 
Upvote 0
MEP1984 said:
Hi, below in bold are my replies:

1. Do we need the first block of code that remove borders, clear contents and removes shading? --> As I am planning to use sheet macro every month, this is to ensure that the old data is deleted and we start on a blank page. Any advise on optimising this will be helpful.
2. what row does your data start on? - In each sheet the data starts on row 4. Actual values are on row 6, there are blank rows through the rows down, which should not be taken into concideration. I mean, despite them, we need to have the whole data filtered later
3. what column does your data start in? - In each sheet, column A.
4. what column does your data end in? -Flexible, depending on the month. For November, Column S, as T is an important note. So in Dec it should end in T, and U will be the note.
5. Other than the sheet Macro are there any other sheets you are working in? - the sheets per months, in which I add column with comparison of the data ex. (=AND(($H6=$I6),($I6=$J6),($J6=$K6),($K6=$L6),($L6=$M6),($M6=$N6),($N6=$O6),($O6=$P6),($P6=$Q6),($Q6=$R6),($R6=$S6)) I only need information for the "false" result. Where the data matches, everything remains the same.
6. I see three actions of import here. A formula that is entered and then copied down (the formula in column AO), a filter on false in column AO (yes), and finally a copying of the filter cells to range F3 of the marco sheet (Range F3 is random. Could be E3,C2... as long as A and B are free). Am I missing any other important actions?- After copying, the data should be transposed in sheet macro with values only
Click to expand...

ok so the following steps

1. Clear the macro sheet
2. Determine the monthly sheet using A2 (A2 of which sheet? Macro? if so when is this value populated?)
3. Determine the last row of data of monthly sheet
4. determine the last column of data of monthly sheet
5. Add a column to the monthly sheet and add comparison formulas (see below)
6. Filter monthly sheet to only show false values
7. Copy visible cells only
8. Copy only the value to the Macro sheet (which cell?) but the data needs to be transposed.

following formula
Code: 
(=AND(($H6=$I6),($I6=$J6),($J6=$K6),($K6=$L6),($L6=$M6),($M6=$N6),($N6=$O6),($O6=$P6),($P6=$Q6),($Q6=$R6),($R6=$S6))

so in the above formula....

you are comparing the previous months to each other and if any month does not reconcile the answer is false, correct?
 
Upvote 0
1. Clear the macro sheet
2. Determine the monthly sheet using A2 (A2 of which sheet? Macro? if so when is this value populated?)-A2, sheet Macro, populated manually.
3. Determine the last row of data of monthly sheet - let it be 600
4. determine the last column of data of monthly sheet - AN
5. Add a column to the monthly sheet and add comparison formulas (see below)
6. Filter monthly sheet to only show false values
7. Copy visible cells only
8. Copy only the value to the Macro sheet (which cell?) but the data needs to be transposed. Copy the filtered value from the respective month sheet from cell A4 to cell AN600 let's say.

following formula
Code:
(=AND(($H6=$I6),($I6=$J6),($J6=$K6),($K6=$L6),($L6=$M6),($M6=$N6),($N6=$O6),($O6=$P6),($P6=$Q6),($Q6=$R6),($R6=$S6))
so in the above formula....

you are comparing the previous months to each other and if any month does not reconcile the answer is false, correct? exactly.
 
Upvote 0
MEP1984 said:
1. Clear the macro sheet
2. Determine the monthly sheet using A2 (A2 of which sheet? Macro? if so when is this value populated?)-A2, sheet Macro, populated manually.
3. Determine the last row of data of monthly sheet - let it be 600
4. determine the last column of data of monthly sheet - AN
5. Add a column to the monthly sheet and add comparison formulas (see below)
6. Filter monthly sheet to only show false values
7. Copy visible cells only
8. Copy only the value to the Macro sheet (which cell?) but the data needs to be transposed. Copy the filtered value from the respective month sheet from cell A4 to cell AN600 let's say.

following formula
Code:
(=AND(($H6=$I6),($I6=$J6),($J6=$K6),($K6=$L6),($L6=$M6),($M6=$N6),($N6=$O6),($O6=$P6),($P6=$Q6),($Q6=$R6),($R6=$S6))
so in the above formula....

you are comparing the previous months to each other and if any month does not reconcile the answer is false, correct? exactly.
Click to expand...

ok try this.

Code: 
Sub mep1984()
Dim wsM As Worksheet, ws As Worksheet
Dim lngrow As Long, lngcol As Long, lngrowst As Long
Dim rnghead As Range, rng As Range, cell As Range
Dim intCOL As Integer
Dim strMTH As String
    strFORMULA = "Comparison" 'Change this to the formula header name
    lngrowst = 4
    Set wsM = Sheets("Macro") 'change this to Macro or whatever you macro sheet is called
    With wsM
        strMTH = wsM.Range("A2")
        If strMTH = "" Then
            MsgBox "Please enter a value into cell A2 to represent the " _
                        & "month year (ie: Nov16)." _
                        & vbNewLine & "Then start again."
            End
        End If
        lngrow = wsM.Range("A" & wsM.Rows.Count).End(xlUp).Row
        If Not lngrow = lngrowst Then
            lngcol = wsM.Cells(4, wsM.Columns.Count).End(xlToLeft).Column
            
            Set rng = wsM.Range(wsM.Cells(lngrowst, 1), wsM.Cells(lngrow, lngcol))
            rng.Delete shift:=xlUp
        End If
    End With
    
    Set ws = Sheets(strMTH)
    ws.Select
    With ws
        lngrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
        lngcol = ws.Cells(4, ws.Columns.Count).End(xlToLeft).Column
        intCOL = lngcol + 1
        ws.Cells(lngrowst, intCOL).Value = "Comparison" 'change to new field header
        Set rng = ws.Range(ws.Cells(lngrowst + 2, intCOL), ws.Cells(lngrow, intCOL))
        rng.FormulaR1C1 = "=AND(RC[-12]=RC[-11],RC[-11]=RC[-10],RC[-10]=RC[-9]," _
                            & "RC[-9]=RC[-8],RC[-8]=RC[-7],RC[-7]=RC[-6]," _
                            & "RC[-6]=RC[-5],RC[-5]=RC[-4],RC[-4]=RC[-3]," _
                            & "RC[-3]=RC[-2],RC[-2]=RC[-1])"
        Set rnghead = ws.Range(ws.Cells(lngrowst, 1), ws.Cells(lngrowst, intCOL))
        Set rng = ws.Range(ws.Cells(lngrowst, 1), ws.Cells(lngrow, lngcol))
        .AutoFilterMode = False
        rnghead.AutoFilter field:=intCOL, Criteria1:="FALSE"
        rng.SpecialCells(xlCellTypeVisible).Copy
        wsM.Range("A4").PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlNone, _
            SkipBlanks:=True, _
            Transpose:=True
    End With
    wsM.Select
End Sub
 
Upvote 0
I fixed your comparison formula so it is more dynamic to the data set. so try this code instead

Code: 
Sub mep1984()
Dim wsM As Worksheet, ws As Worksheet
Dim lngrow As Long, lngcol As Long, lngrowst As Long
Dim rnghead As Range, rng As Range, cell As Range
Dim intCOL As Integer, intMTH1 As Integer, intMTH2 As Integer, _
    intST As Integer, intEND As Integer
Dim strMTH As String
    strMTH = "Comparison" 'Change this to the formula header name
    lngrowst = 4
    Set wsM = Sheets("MEP1984 - macro") 'change this to Macro
    With wsM
        strMTH = wsM.Range("A2")
        If strMTH = "" Then
            MsgBox "Please enter a value into cell A2 to represent the " _
                        & "month year (ie: Nov16)." _
                        & vbNewLine & "Then start again."
            End
        End If
        lngrow = wsM.Range("A" & wsM.Rows.Count).End(xlUp).Row
        If Not lngrow = lngrowst - 2 Then
        
            lngcol = wsM.Cells(4, wsM.Columns.Count).End(xlToLeft).Column
            
            Set rng = wsM.Range(wsM.Cells(lngrowst, 1), wsM.Cells(lngrow, lngcol))
            rng.Delete shift:=xlUp
        End If
    End With
    
    Set ws = Sheets(strMTH)
    ws.Select
    With ws
        lngrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
        lngcol = ws.Cells(4, ws.Columns.Count).End(xlToLeft).Column
        intCOL = lngcol + 1
        ws.Cells(lngrowst, intCOL).Value = "Comparison" 'change to new field header
        Set rnghead = ws.Range(ws.Cells(lngrowst, 1), ws.Cells(lngrowst, intCOL))
        intMTH2 = lngcol
        intMTH1 = rnghead.Find("Jan").Column    'Change this to the header of the first column to be compared by the formula
        Set rng = ws.Range(ws.Cells(lngrowst + 2, intCOL), _
            ws.Cells(lngrow, intCOL))
        intST = intMTH1 - intCOL
        intEND = intMTH2 - intCOL
        rng.Formula = "=IF(SUM(IF(FREQUENCY(RC[" & intST & "]:RC[" _
                        & intEND & "],RC[" & intST & "]:RC[" & intEND _
                        & "])>0,1))>1,""False"","""")"
        rng.Copy
        rng.PasteSpecial xlPasteValues
        
        Set rng = ws.Range(ws.Cells(lngrowst, 1), ws.Cells(lngrow, lngcol))
        .AutoFilterMode = False
        rnghead.AutoFilter field:=intCOL, Criteria1:="False"
        rng.SpecialCells(xlCellTypeVisible).Copy
        wsM.Range("A4").PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlNone, _
            SkipBlanks:=True, _
            Transpose:=True
    End With
    wsM.Select
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Macro to load 10 files from different folder into different sheets in Master File
Replies
2
Views
135
joxyin
J
B
  • Question
Paste results of Filter Function in place, or in separate sheet
Replies
0
Views
98
bertible
B
V
  • Question
Looking to create VBA command to go through List of sheet names, look up sheet name in range, look up value in cell next to it, and run a macro
Replies
3
Views
102
Volrin
V
K
  • Question
Excel script using if statement to copy/paste special values for specific rows
Replies
2
Views
177
kcampbell675
K
J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
165
shknbk2
S

Forum statistics

Threads
1,214,897
Messages
6,122,141
Members
449,066
Latest member
Andyg666

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top