VBA Macro to copy and concatenate some columns from some rows of one worksheet to a different workbook

[scotchy]

Hello experts ... I'm a developer but have no experience with macros in excel and hoping someone here can save me some time.

I need to create a button on one worksheet that will :

- create a new workbook ... call it 'c:\sheets\Newbook.xls' ... (or open an existing one and empty it out)
- search through all rows of the current worksheet and select those that have some data in column A
- note that some rows may be completely blank
- copy some of the data from these rows ... columns A & B concatenated and capitalised, col D and col F
- paste the three resulting pieces of data into sheet 1 of 'Newbook' in columns A,B and C
- have no empty rows in Newbook

I know how to create the button to execute the Macro etc. but the syntax and methods of macros are a complete mystery to me.

Thankful for any and all help.

 

[JLGWhiz]

Sub newFile()
Dim wb As Workbook, sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = ActiveSheet
Set wb = Workbooks.Add
Set sh2 = wb.Sheets(1)
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        If c <> "" Then
            sh2.Cells(Rows.Count, 1).End(xlUp)(2) = UCase(c.Value) & UCase(c.Offset(, 1).Value)
            sh2.Cells(Rows.Count, 1).End(xlUp).Offset(, 1) = c.Offset(, 3).Value
            sh2.Cells(Rows.Count, 1).End(xlUp).Offset(, 2) = c.Offset(, 5).Value
        End If
    Next
End Sub

Copy to standard code module 1.

 

[scotchy]

Thanks JLGWhiz for responding ... I'm going to try it out now. I'm not quite sure from this where or how I specify the path of the other Workbook. I'm trying to build a very simple result sheet for an end user so I'd prefer if the other sheet was always with the same name and in the same place and simply opened,emptied and rebuilt each time the Macro runs.

 

[JLGWhiz]

Thanks JLGWhiz for responding ... I'm going to try it out now. I'm not quite sure from this where or how I specify the path of the other Workbook. I'm trying to build a very simple result sheet for an end user so I'd prefer if the other sheet was always with the same name and in the same place and simply opened,emptied and rebuilt each time the Macro runs.

Based on the objective outlined in post #3, if it were my project I would first develop my workbooks with headers, formats and column widths, give the file a name and then develop the code to populate the workbook from a source document. It would be much easier to communicate the logical data extraction, copy, paste, etc. if you had a file name, worksheet name and column designations to refer to in the threads where you ask for assistance.

 

[scotchy]

Thanks again JLGWhiz. I used your code and after a bit more research I ended up with the below code which works perfectly for me.

I really just wanted to create a new sheet with the same two columns every time which could then be uploaded to an online product we use.

Simply overwriting the same location and name every time without prompting did the trick.

Cheers.


Sub upload_click()


Dim wb As Workbook, sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = ActiveSheet
Set wb = Workbooks.Add
Set sh2 = wb.Sheets(1)


sh2.Cells(1, 1) = "Name"
sh2.Cells(1, 2) = "Full Address"


For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
If c.Offset(, 3) = "H" Then
sh2.Cells(Rows.Count, 1).End(xlUp)(2) = UCase(c.Value) & " - " & UCase(c.Offset(, 1).Value)
sh2.Cells(Rows.Count, 1).End(xlUp).Offset(, 1) = UCase(c.Offset(, 2).Value & " " & c.Offset(, 5).Value)
End If
Next

Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Runs\upload.xlsx", FileFormat:=xlOpenXMLWorkbook, _
CreateBackup:=False, AccessMode:=xlExclusive, _
ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
Application.DisplayAlerts = True


wb.Close (True)


End Sub

 

[JLGWhiz]

Thanks again JLGWhiz. I used your code and after a bit more research I ended up with the below code which works perfectly for me.

I really just wanted to create a new sheet with the same two columns every time which could then be uploaded to an online product we use.

Simply overwriting the same location and name every time without prompting did the trick.

Cheers.


Sub upload_click()


Dim wb As Workbook, sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = ActiveSheet
Set wb = Workbooks.Add
Set sh2 = wb.Sheets(1)


sh2.Cells(1, 1) = "Name"
sh2.Cells(1, 2) = "Full Address"


For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
If c.Offset(, 3) = "H" Then
sh2.Cells(Rows.Count, 1).End(xlUp)(2) = UCase(c.Value) & " - " & UCase(c.Offset(, 1).Value)
sh2.Cells(Rows.Count, 1).End(xlUp).Offset(, 1) = UCase(c.Offset(, 2).Value & " " & c.Offset(, 5).Value)
End If
Next

Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Runs\upload.xlsx", FileFormat:=xlOpenXMLWorkbook, _
CreateBackup:=False, AccessMode:=xlExclusive, _
ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
Application.DisplayAlerts = True


wb.Close (True)


End Sub

Glad you worked it out. BTW, you can put the Code tags on the code you post by highlighting (selecting) it with the mouse pointer then click the Pound (Hashtag) symbol on the toolbar. That allows the indenture and other features to remain, whereas some things are lost if code tags are not used.
Regards, JLG