| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code not working correctly
Hi,
I have two problems with the same code - I am finding that when i run the macro, it copies the specified cells into the report sheet but it doesnt copy from the 1st cell/row under the column header row.. (for examples, intead of copying from C2 it copies from C3 onwards.. but there is a filter on so C2 is not actually C2 but something else - C**- dont know if this matters). The code from where it selects the cells from: (before this bit of the code there are some conditions - if cell =blank or 0 then hide, dont know if this will affect anything.) 'selects columns For Each cell In Range("Y2:Y65536") If cell.Rows.Hidden = False Then If cell.Value "" Then Range("C" & cell.Rows.Row & ":" & "D" & cell.Rows.Row & "," _ & "G" & cell.Rows.Row & ":" & "H" & cell.Rows.Row & "," _ & "S" & cell.Rows.Row & ":" & "Y" & cell.Rows.Row & "," _ & "AJ" & cell.Rows.Row & ":" & "AJ" & cell.Rows.Row).Select 'paste them to other sheet Selection.Copy Worksheets("Report").Activate If Range("A1") = "" Then Worksheets("GTS_C_SI").Activate Range("C1:D1,G1:H1,S1:Y1,AJ1").Select Selection.Copy Worksheets("Report").Activate Range("A1").Select Selection.PasteSpecial Paste:=xlPasteAll Application.CutCopyMode = False Else Range("A65536").End(xlUp).Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteAll Application.CutCopyMode = False End If Worksheets("GTS_C_SI").Activate End If End If Next cell Worksheets("Report").Activate Cells.Select Cells.EntireColumn.AutoFit Cells.EntireRow.AutoFit Range("A1").Select Worksheets("GTS_C_SI").Activate With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With End Sub The second problem I am having is with the below line: Range("C1:D1,G1:H1,S1:Y1,AJ1").Select Everything in the above is fine just that i dont want it to select S1:Y1 - which selects everything between S&Y. I want it to just select S and Y. I have tried changing the code to just S1 and Y1 and numerous other things but i get strange results! like the info is there but the column header is missing and info all mixed up. Please help. Thanks! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code not working correctly
The second problem I replace a : with a comma in two lines
& "S" & cell.Rows.Row & "," & "Y" & cell.Rows.Row & "," _ and Range("C1:D1,G1:H1,S1,Y1,AJ1").Select The first problem I think row 2 is hidden and that is why it is not getting copied. the code is copying Row 3 to row 3 and skipping the hidden row. For Each cell In Range("Y2:Y65536") If cell.Rows.Hidden = False Then If cell.Value "" Then Range("C" & cell.Rows.Row & ":" & "D" & cell.Rows.Row & "," _ & "G" & cell.Rows.Row & ":" & "H" & cell.Rows.Row & "," _ & "S" & cell.Rows.Row & "," & "Y" & cell.Rows.Row & "," _ & "AJ" & cell.Rows.Row & ":" & "AJ" & cell.Rows.Row).Select 'paste them to other sheet Selection.Copy Worksheets("Report").Activate If Range("A1") = "" Then Worksheets("GTS_C_SI").Activate Range("C1:D1,G1:H1,S1,Y1,AJ1").Select Selection.Copy Worksheets("Report").Activate Range("A1").Select Selection.PasteSpecial Paste:=xlPasteAll Application.CutCopyMode = False Else Range("A65536").End(xlUp).Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteAll Application.CutCopyMode = False End If Worksheets("GTS_C_SI").Activate End If End If Next cell Worksheets("Report").Activate Cells.Select Cells.EntireColumn.AutoFit Cells.EntireRow.AutoFit Range("A1").Select Worksheets("GTS_C_SI").Activate With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With End Sub "Zak" wrote: Hi, I have two problems with the same code - I am finding that when i run the macro, it copies the specified cells into the report sheet but it doesnt copy from the 1st cell/row under the column header row.. (for examples, intead of copying from C2 it copies from C3 onwards.. but there is a filter on so C2 is not actually C2 but something else - C**- dont know if this matters). The code from where it selects the cells from: (before this bit of the code there are some conditions - if cell =blank or 0 then hide, dont know if this will affect anything.) 'selects columns For Each cell In Range("Y2:Y65536") If cell.Rows.Hidden = False Then If cell.Value "" Then Range("C" & cell.Rows.Row & ":" & "D" & cell.Rows.Row & "," _ & "G" & cell.Rows.Row & ":" & "H" & cell.Rows.Row & "," _ & "S" & cell.Rows.Row & ":" & "Y" & cell.Rows.Row & "," _ & "AJ" & cell.Rows.Row & ":" & "AJ" & cell.Rows.Row).Select 'paste them to other sheet Selection.Copy Worksheets("Report").Activate If Range("A1") = "" Then Worksheets("GTS_C_SI").Activate Range("C1:D1,G1:H1,S1:Y1,AJ1").Select Selection.Copy Worksheets("Report").Activate Range("A1").Select Selection.PasteSpecial Paste:=xlPasteAll Application.CutCopyMode = False Else Range("A65536").End(xlUp).Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteAll Application.CutCopyMode = False End If Worksheets("GTS_C_SI").Activate End If End If Next cell Worksheets("Report").Activate Cells.Select Cells.EntireColumn.AutoFit Cells.EntireRow.AutoFit Range("A1").Select Worksheets("GTS_C_SI").Activate With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With End Sub The second problem I am having is with the below line: Range("C1:D1,G1:H1,S1:Y1,AJ1").Select Everything in the above is fine just that i dont want it to select S1:Y1 - which selects everything between S&Y. I want it to just select S and Y. I have tried changing the code to just S1 and Y1 and numerous other things but i get strange results! like the info is there but the column header is missing and info all mixed up. Please help. Thanks! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code not working correctly
I think one of the problems are solved but the one with the hidden row isnt.
You said the code is copying from row 3 to row 3, i dont know where in the code it does that so can you please show me how to change so it copies from row 2 to 2? Row 2 at present is not hidden, it is visible like all the others so i dont understand why it doesnt copy that across. There are conditions in the code that will hide rows if cells in column Y are blank and OR 0. but after this filter is done row 2 is showing.. it is not hidden, then why doesnt the macro reflect this? Also, when i used your revised code one of the columns (when copied across) lost its formula and instead of pasting values as it should, it pasted formula but formula didnt recognise cells and now those cell have 'VALUE' in them. Please help! "Joel" wrote: The second problem I replace a : with a comma in two lines & "S" & cell.Rows.Row & "," & "Y" & cell.Rows.Row & "," _ and Range("C1:D1,G1:H1,S1,Y1,AJ1").Select The first problem I think row 2 is hidden and that is why it is not getting copied. the code is copying Row 3 to row 3 and skipping the hidden row. For Each cell In Range("Y2:Y65536") If cell.Rows.Hidden = False Then If cell.Value "" Then Range("C" & cell.Rows.Row & ":" & "D" & cell.Rows.Row & "," _ & "G" & cell.Rows.Row & ":" & "H" & cell.Rows.Row & "," _ & "S" & cell.Rows.Row & "," & "Y" & cell.Rows.Row & "," _ & "AJ" & cell.Rows.Row & ":" & "AJ" & cell.Rows.Row).Select 'paste them to other sheet Selection.Copy Worksheets("Report").Activate If Range("A1") = "" Then Worksheets("GTS_C_SI").Activate Range("C1:D1,G1:H1,S1,Y1,AJ1").Select Selection.Copy Worksheets("Report").Activate Range("A1").Select Selection.PasteSpecial Paste:=xlPasteAll Application.CutCopyMode = False Else Range("A65536").End(xlUp).Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteAll Application.CutCopyMode = False End If Worksheets("GTS_C_SI").Activate End If End If Next cell Worksheets("Report").Activate Cells.Select Cells.EntireColumn.AutoFit Cells.EntireRow.AutoFit Range("A1").Select Worksheets("GTS_C_SI").Activate With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With End Sub "Zak" wrote: Hi, I have two problems with the same code - I am finding that when i run the macro, it copies the specified cells into the report sheet but it doesnt copy from the 1st cell/row under the column header row.. (for examples, intead of copying from C2 it copies from C3 onwards.. but there is a filter on so C2 is not actually C2 but something else - C**- dont know if this matters). The code from where it selects the cells from: (before this bit of the code there are some conditions - if cell =blank or 0 then hide, dont know if this will affect anything.) 'selects columns For Each cell In Range("Y2:Y65536") If cell.Rows.Hidden = False Then If cell.Value "" Then Range("C" & cell.Rows.Row & ":" & "D" & cell.Rows.Row & "," _ & "G" & cell.Rows.Row & ":" & "H" & cell.Rows.Row & "," _ & "S" & cell.Rows.Row & ":" & "Y" & cell.Rows.Row & "," _ & "AJ" & cell.Rows.Row & ":" & "AJ" & cell.Rows.Row).Select 'paste them to other sheet Selection.Copy Worksheets("Report").Activate If Range("A1") = "" Then Worksheets("GTS_C_SI").Activate Range("C1:D1,G1:H1,S1:Y1,AJ1").Select Selection.Copy Worksheets("Report").Activate Range("A1").Select Selection.PasteSpecial Paste:=xlPasteAll Application.CutCopyMode = False Else Range("A65536").End(xlUp).Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteAll Application.CutCopyMode = False End If Worksheets("GTS_C_SI").Activate End If End If Next cell Worksheets("Report").Activate Cells.Select Cells.EntireColumn.AutoFit Cells.EntireRow.AutoFit Range("A1").Select Worksheets("GTS_C_SI").Activate With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With End Sub The second problem I am having is with the below line: Range("C1:D1,G1:H1,S1:Y1,AJ1").Select Everything in the above is fine just that i dont want it to select S1:Y1 - which selects everything between S&Y. I want it to just select S and Y. I have tried changing the code to just S1 and Y1 and numerous other things but i get strange results! like the info is there but the column header is missing and info all mixed up. Please help. Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUM formula not working correctly | Excel Worksheet Functions | |||
| code not working correctly | Excel Programming | |||
| Cursor not working correctly | Excel Discussion (Misc queries) | |||
| VBE Window not working correctly | Excel Programming | |||
| Column find code not working correctly | Excel Programming | |||
Linear Mode
