copy non contiguous 16 cells to a row in another sheet

[maniniinfotech]

hi i have vba code but it is copying the data side to side
data range is A1 : F421
data to be copied in another sheet
A1 : P1
A2 : P2 and so on

data should be copied from A1,B1,C1,D1,E1,F1,A2,B2,C2,D2,E2,F2,A3,B3,C3,D3 of sheet1
data should be pasted to A1 to P1 of sheet 2

please find the attachement

 

[Marc L]

Hi !​

​

You can use the Range.Copy method, to see in VBA inner help.​

In case of issue, attach at least an expected result workbook …​

​

 

[maniniinfotech]

hi am sorry and i do not know vba coding
i found that code in google and tried altering it but i could not do it
forgot to upload the file please find the attachment

 

[Marc L]

1. Destination result : Sheet1 or Sheet2 ?!
   
   
2. Does it need to keep the original formatting or only values is enough ?

 

[maniniinfotech]

sheet 2 and original formatting as it does not accept 0 value before the no

 

[Marc L]

As a beginner starter :​

Sub Demo1()
        Dim C&
        Application.ScreenUpdating = False
    With Sheet1.[A1].CurrentRegion
        For C = 1 To .Count:  .Cells(C).Copy Sheet2.Columns("A:P").Cells(C):  Next
    End With
        Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[maniniinfotech]

thank you so much Marc L
its awesome and superb

can i ask another requirement if u do not mind
thank you in advance

 

[Marc L]

If it's relative to the same attachment, why not ?​

Or according to the forum rules open a new thread …​

​

 

[maniniinfotech]

thank you so much for your support Marc L
normally we use find option for the selection or the sheet
is there any possibility to find 3 cells at a time

example
if data is from a1: p167
it needs to search given three cells data in any form like Sudoku
if i highlight 28 in given range then i can find
74,28,93
74,28,95
74,28,62

use highlight eual to and use 28 in the geven attachment sheet 3

 

[Marc L]

is there any possibility to find 3 cells at a time
[…]
if i highlight 28 in given range then

If you 'highlight' - clearly what that means ?! - 28 so you are searching only for one cell and not 3 …​

​

 

[maniniinfotech]

hi Marc L
to make u understand i said the particular no that is 28
now in the given attachment
find
51,87,20
14,62,18
47,24,28
75,17,00
thank you in advance

 

[Marc L]

Maybe unclear as none of your samples matches with your Sheet2 last attachment …​

​

If it's about searching 3 consecutives cells values in Sheet2 columns A:F for example,​

the idea is to use the Range.Find method on the first 4 columns (A:D) to locate the first value​

then check if both next cells match with the desired sequence.​

​

And what's the purpose, just find or what next after ?​

​

 

[maniniinfotech]

hi Marc L its sheet 3 and range is (R1 :AG169)
sequence is non contiguous
in the given attachment i have highlighted the cells
as how result is expected

 

[salim hasan]

Try this macro
(without for next loop)
(without copy and paste)

Option Explicit
Sub Salim_Macro()
  Sheet2.[A1].CurrentRegion.ClearContents
  Dim Range_To_Copy As Range
  Set Range_To_Copy = Sheet1.[A1].CurrentRegion

  Dim Max_ro: Max_ro = Range_To_Copy.Rows.Count
  Dim Max_col: Max_col = Range_To_Copy.Columns.Count

  Sheet2.[A1].Resize(Max_ro, Max_col).Value = _
  Range_To_Copy.Value

End Sub

 

[Marc L]

Salim, some points about your code :​

• Your result is not the expected layout result, did you at least try and compare with the expected ?
  
  
• For a clean code : regroup any Dim statement at the beginning of the procedure …
  
  
• For a very clean code : if you declare any object variable, do not forget to release it before the end (set to Nothing) …
  
  
• Instead of an object variable you can consider the With statement, in particular when you forgot to release any object variable …

 

[Marc L]

i have highlighted the cells
as how result is expected

So the idea stays the same : use the Range.Find method to locate the first value​

then for each direction check if the near cell is equal to the second value,​

if yes go on for the third and so on … When 4 cells are matching, highlight them.​

​

If you are in trouble, as this is far from the initial question, open a new thread​

with a crystal clear explanation from the input - after all, we are not mind readers - from cells, from an input box, ? …​

to the result via the search and directions and attach a workbook with a before worksheet and​

an after (expected) worksheet in order some helpers will be interested to solve your thread.​

As the better initial explanation and attachment, the quicker & more targeted answer …​

​

 

[maniniinfotech]

ok thank you Marc L and please guide me to learn vba
am completely new to vba
i do not know coding in vba

 

[maniniinfotech]

kindly please code it and provide me as i do not understand anything about strings ,variables and all

 

[maniniinfotech]

Hi Marc L
I think input box helps and to set the directions how do I code

 

[Marc L]

Open the VBA inner help to see explanation & samples for Range.Find, Range.Offset and InputBox …​

Documentation & samples are also in the Microsoft Docs website.​

You can also use the Macro Recorder in order to get a code base …​

​

Excel VBA - Information, Tutorials, Examples & Resources » Chandoo.org - Learn Excel, Power BI & Charting Online

Excel VBA – Information, Tutorials, Examples & Resources Excel VBA allows you to automate various activities you do in Excel. We use Excel to analyze data, create reports, prepare charts & presentations, do calculations and understand information. When we are doing all these, we spend quite some...

chandoo.org