A macro to create new spreadsheet and copy data

Closed
Vasu - Feb 28, 2014 at 01:36 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Mar 4, 2014 at 11:17 AM
Hello,

In the example below, I want to copy all the date from all the worksheets present in excel. In addition to my requirement to edit value of few columns before creating new spreadsheet.

PFB the sample data

Exisiting worksheet

ColumnA ColumnB ColumnC
1 PTCC1234 R008
2 PTCC4567 R008
3 PTCC4568 R008
4 PTCC9854 R008


New worksheet

ColumnA ColumnB ColumnC
1 ICC1234 K11
2 ICC4567 K11
3 ICC4568 K11
4 ICC9854 K11


Would really appreciate quick response on this.
Many Thanks,
Vasu

1 reply

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Mar 4, 2014 at 11:17 AM
Hi Vasu,

Try the following code and let me know if the desired result shows up:
Sub RunMe()
Dim ws As Worksheet

Sheets.Add(after:=Sheets(Sheets.Count)).Name = "Result"

For Each ws In Worksheets
If ws.Name <> "Result" Then
ws.Range("A1").CurrentRegion.Copy Sheets("Result").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next ws

Sheets("Result").Activate
Columns("B:B").Replace What:="PT", Replacement:="I", LookAt:=xlPart, SearchOrder:=xlByRows
Columns("C:C").Replace What:="R008", Replacement:="K11", LookAt:=xlPart, SearchOrder:=xlByRows

End Sub

Best regards,
Trowa
0