Excel VBA question

[zebehm]

I'm trying to create a macro that will help me sort data. I take a table of information from a website and import it into a worksheet where I will draw information from it. I'd like the macro to have an if statement where depending on the value of a particular cell in the row it will copy values of a few cells from that row and place them in the right worksheet. Is that possible?


For instance:

If in worksheet1 cell A2 = 1234, copy cells D2, G2, H2 and I2 to worksheet2 starting in cell B3.

Thanks for the help.

 

[JLGWhiz]

Something like this.

Sub cpyCels()  'Example
Dim sh1 As Worksheet, sh2 As Worksheet, cy As Range
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
If sh1.Rangne("A2") = 1234 Then
 `With sh2
  Set cy = Union(.Range("D2"), .Range("G2:I2"))
  cy.Copy sh2.Ramge("B3")
  'Or, if you want it all posted in column B
  'cy.Copy
  'sh2.Range("B3").PasteSpecial Transpose:=True
 End With
End If
End Sub

 

[JLGWhiz]

I'm trying to create a macro that will help me sort data. I take a table of information from a website and import it into a worksheet where I will draw information from it. I'd like the macro to have an if statement where depending on the value of a particular cell in the row it will copy values of a few cells from that row and place them in the right worksheet. Is that possible?


For instance:

If in worksheet1 cell A2 = 1234, copy cells D2, G2, H2 and I2 to worksheet2 starting in cell B3.

Thanks for the help.

These work a lot better without all the typos.

Sub cpyCels()  'Example
Dim sh1 As Worksheet, sh2 As Worksheet, cy As Range
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
If sh1.Range("A2") = 1234 Then
 With sh1
  Set cy = Union(.Range("D2"), .Range("G2:I2"))
  cy.Copy sh2.Range("B3")
  'Or, if you want it all posted in column B
  'cy.Copy
  'sh2.Range("B3").PasteSpecial Transpose:=True
 End With
End If
End Sub

 

[zebehm]

A few spelling errors in the code I fixed and got rid of the ` before the With sh2 and it runs, but there is no output when A2 = 1234, I tried deleting the ' before cy.Copy and sh2.Range("B3").PasteSpecial Transpose:=True but all that did was highlight the cells.

EDIT: Your updated code works great. Thanks!

New question: Is it possible to create a loop to go through about 20 rows to check to see if the first cell = 1234 and then copy the same cells as before to the second worksheet?

 

[zebehm]

I believe I have found a way to incorporate a loop. Thanks again for your help JLG!

 

[JLGWhiz]

you're welcome,
Regards, JLG