1

I am new to vba and need a little help. I want to copy copy column A & C to sheet 2 but my concatenation syntax (my_range = rng1&":"&rng2) won't work.
I have tried other syntax too but it's just syntax to concatenate Strings into a single column and that's what I am looking for. What I want is Column A & C from sheet 1 to be copied in Column A & B in sheet 2.

Sub CommandButton1_Click()
Dim my_range As String, rng1 As String, rng2 As String

search_value = Sheets(2).Cells(i, 1).Value = 1

Sheets(1).Activate

For i = 2 To 100

If Sheets(1).Cells(i, 1).Value = search_value Then

rng1 = "A" & i
rng2 = "C" & i
my_range = rng1&":"&rng2

Sheets(1).Rande(my_range).Select
Selection.Copy
Sheets(2).Activate
Sheets(2).Range("A2").Select
Selection.PasteSpecial: xlPasteAll , SkipBlanks:=True, Transpose:=False

End If

Next
Application.CutCopyMode = False
Sheets(2).Cells(1, 2).Select

End Sub
Dominique
  • 16,450
  • 15
  • 56
  • 112
Md101
  • 15
  • 5
  • 3
    You need some spaces: `my_range = rng1 & ":" & rng2` – braX Aug 17 '22 at 08:17
  • `Sheets(1).Rande(my_range).Select` : is that a typo when copying to StackOverflow? (i.e. does it say `Sheets(1).Range` instead of `Sheets(1).Rande` in your actual code) – Chronocidal Aug 17 '22 at 08:39
  • Thanks alot! Could you also help me with the logic to Copy columns A, C, D from Sheet 1 if value = 1 in column A to Sheet 2 beginning from A2. And if in Sheet 1 column A value = 11 then copy columns A, C, D to Sheet 3 beginning From A2. – Md101 Aug 17 '22 at 08:41
  • Oh yes there are some Typos. I have corrected them in my final code. – Md101 Aug 17 '22 at 08:43
  • `search_value = Sheets(2).Cells(i, 1).Value = 1` - you're using `i` before it's given a value in the loop. At that point `i` will equal 0 and there's isn't a row 0 on the sheet. That line will also return TRUE/FALSE as it's asking if Cells(i,1) is equal to 1. – Darren Bartrup-Cook Aug 17 '22 at 09:24

2 Answers2

1

The simplest way to create a range from one cell to the other is the following:

my_range = Range(rng1, rng2)

(I found some examples on this website.)

Dominique
  • 16,450
  • 15
  • 56
  • 112
  • Thanks alot! Could you also help me with the logic to Copy columns A, C, D from Sheet 1 if value = 1 in column A to Sheet 2 beginning from A2. And if in Sheet 1 column A value = 11 then copy columns A, C, D to Sheet 3 beginning From A2. – Md101 Aug 17 '22 at 08:58
  • 1
    @Mel: you can find quite some examples on this in this question: "https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba", I've written an answer, based on your particular kind of question (see answer "How to avoid copy-paste?"). – Dominique Aug 17 '22 at 09:45
0

Do it like below :

rng1 = "A" & CStr(i)
rng2 = "C" & CStr(i)
my_range = rng1 & ":" & rng2
Md. Suman Kabir
  • 5,243
  • 5
  • 25
  • 43