Excel VBA to Copy non-blank cell values to another sheet

[astrid22]

Hi, I have a sheet with a table with two columns: the first column says countries and the second column says fruits.

[LEFT][COLOR=#222222][FONT=Verdana]Countries              Fruits[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]South Korea [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]China  [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]United States [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Russia  [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Australia  [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Singapore[/FONT][/COLOR][/LEFT]

If I enter values on some of the fruits for some countries and leave the other rows blank, I want the countries with specified fruits be copied in a new table in another sheet. Like for example:

Countries               Fruits
South Korea                      Orange
China  
United States                    Apple
Russia                          Pineapple
Australia  
Singapore                       Grapes

​

In the new sheet I want it to look like this:

Countries               Fruits
South Korea             Orange
United States           Apple
Russia                  Pineapple
Singapore               Grapes

​

I am relatively new to Excel VBA and I have absolutely no idea how to do this. Please help!

 

[mumps]

I am assuming that your data is in columns A and B of Sheet1 and that Sheet2 has the same headers as Sheet1.
Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in column B of Sheet1 and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Range("A" & Target.Row).Resize(, 2).Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End Sub

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG04Mar44
[COLOR="Navy"]Dim[/COLOR] r [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] r = Range("B:B").SpecialCells(xlCellTypeConstants)
r.Copy Sheets("Sheet2").Range("B1")
r.Offset(, -1).Copy Sheets("Sheet2").Range("A1")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[astrid22]

Hi, sorry. What I am trying to do is interchange the position of the fruits column and the countries column while copying the countries with specified fruits into the new sheet.
Like this:

Fruits                Countries
Orange                South Korea
Apple                 United States
Pineapple             Russia
Grapes                Singapore

Also, I am trying to incorporate that here:

Sub GenerateConf()
  Dim ws As Worksheet
  Dim NameCount As Long
  Dim NameBase As String
  
  NameBase = Format(Date, "mm.dd.yyyy OA ")
  For Each ws In Worksheets
    If ws.Name Like NameBase & "#*" Then NameCount = NameCount + 1
  Next ws
  Sheets.Add(After:=Sheets(Sheets.Count)).Name = NameBase & NameCount + 1
  Sheets(Sheets.Count).Tab.ColorIndex = 50
End Sub

The fruits I want copied are in merged cells. Like Column A: Countries and then Column B merged with Coulmn C : Fruits.

Any ideas? I tried both codes and they are both working great, I just don't know which one to incorporate with my existing code of inserting a new sheet

 

[mumps]

First, I would suggest that you unmerge any merged cells because merged cells almost always create problems for Excel macros. Can you explain in detail how you are trying to name the sheet that you add? Can you also explain in detail how you want to incorporate the macro I suggested with your existing code? Which of the many sheets contains the countries and fruit and to which sheet do you want to copy the countries with the specified fruits?