copy paste from worksheet to other worksheet from cell B1 to cell A1

[rickmeister]

All,

Could someone please help?

I have a code that copies the worksheet from one workbook and paste it into a new workbook.
However in the input worksheet the text starts at B1, so I would like to copy the entire sheet to start at A1.

Here's what I have so far:


For Each sheetje In ImportWB.Sheets
'Debug.Print sheetje.Name
'Debug.Print sheetje.UsedRange.Address

'Find "Virtual" or "DO_" on the sheet
Set findRNG = sheetje.Range("B1").Find(What:="Virtual")
'Search next value
Set findRNG2 = sheetje.Range("B3").Find(What:="DO_")

If (Not findRNG Is Nothing Or Not findRNG2 Is Nothing) Then

sheetje.Copy After:=NewWorkbook.Sheets(1)



'reset RNG
Set findRNG = Nothing
Set findRNG2 = Nothing
Else
'Nothing found
End If


Next


Thanks a bunch

[AB33]

Please use code tags with your code.

Please Login or Register  to view this content.

why can not you change B1 to A1?

[rickmeister]

in the input I have to find for value B1 for a particular value, If found, he needs to copy paste the entire sheet to A1. Not B1.

I have found a solution , maybe not the most proper one but it works fine :

After the copy I just delete column A

'STEP 8: Loop through the whole discovery report and check for sheets with the value like "%Virtual%" or "DO_%"
For Each sheetje In ImportWB.Sheets
'Debug.Print sheetje.Name
'Debug.Print sheetje.UsedRange.Address

'Find "Column number" on the sheet
Set findRNG = sheetje.Range("B1").Find(What:="Virtual")
'Search next value
Set findRNG2 = sheetje.Range("B3").Find(What:="DO_")

If (Not findRNG Is Nothing Or Not findRNG2 Is Nothing) Then

'Delete column A after loop
sheetje.Copy After:=NewWorkbook.Sheets(1)
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft


'reset RNG
Set findRNG = Nothing
Set findRNG2 = Nothing
Else
'Nothing found
End If


Next