A macro to create new spreadsheet and copy data

[Closed]
Report
-
Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
-
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

Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
482
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