VBA code not work if I change the file name

[KAMRAN AJ]

Hi guys hope you are doing great I pray spend a happy life


My Excel VBA code does not work if change the file name please make changes in the code whatever my file name it works.
Details
I make a code based on the file but I change the file name it is showing me an error.
Sub Macro2()
'
' Macro2 Macro
'


'
Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).Select
Selection.Copy
Windows("Book1").Activate
Range("A2").Select
ActiveSheet.Paste
Windows("File1.xlsx").Activate


Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("C2").Select
ActiveSheet.Paste
Windows("File1.xlsx").Activate


Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("E2").Select
ActiveSheet.Paste
Windows("File1.xlsx").Activate



Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("F2").Select
ActiveSheet.Paste
Windows("File1.xlsx").Activate



Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("G2").Select
ActiveSheet.Paste
Windows("File1.xlsx").Activate



Range("G2:G" & Range("G" & Rows.Count).End(xlUp).Row).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("H2").Select
ActiveSheet.Paste
Windows("File1.xlsx").Activate



Range("H2:H" & Range("H" & Rows.Count).End(xlUp).Row).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("I2").Select
ActiveSheet.Paste
Windows("File1.xlsx").Activate



Range("J2:J" & Range("J" & Rows.Count).End(xlUp).Row).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("J2").Select
ActiveSheet.Paste
Windows("File1.xlsx").Activate

End Sub3079730797

[jolivanes]

This goes for all excel forum sites I know.
Questions that are cross-posted to other web forums must contain links to those posts on those forums or a comment to tell us where else the question has been asked. (A, Z*)

[KAMRAN AJ]

please give a answer i am removing my this post from all other platforms

[Aussiebear]

What other forums did you put this thread to?

[Aussiebear]

This whole thread, along with the others Kamran AJ has posted, in relation to the central issue, has in part been a riddle. If only you had said that you wished to Copy from the Active workbook( which I might add started from "Template A" to finally admitting, an unknown named workbook) an pasting the data to a known workbook "Template B". And that the copy and paste method wasn't a simple task as it involved selected columns of varying row lengths to non similar columns in the new workbook. Possibly then the members would have been inclined to contribute.

So what can we learn from this:

The use of videos in an attempt to describe what you would like to be done. Unique and innovative I must say, but the content and oration within...., simply rambling and confusing to those who were looking at the issue from the outside. I suggest that unless you have a constructed, logical, and scripted methodology , that this is not the way to go.

The use of the terms "Template A" and "Template B" as names for your workbooks. Templates are something that you construct as a foundation layout model, that you then use to add data to and then save as something else. You should never add data to a template then save to the template.

When submitting an issue to VBAX, you need to be mindful of the following concepts. Firstly, have a reasonably clear understanding of the issue, be able to describe the issue in such a way, so as to enable those unfamiliar with your problem, to follow along. Secondly, provide either the particular code that's causing the issue, or a sample workbook/s with enough data contained within, to allow others to understand what you are trying to accomplish. Asking others to compile information from videos or images simply turns people off assisting. The members who contribute here, do so voluntarily, so don't make it hard for them.

If you decide to post the issue elsewhere, on other forums, be up front and honest when doing so. Cross posting on multiple forums without indicating that you have done so, is regarded as very poor forum behaviour. All forums have a variation of the rule regarding cross posting. So please don't become known as a serial cross poster, for in the end no one will ever assist you.

Lastly, the use of the Thread Tools option, "Mark thread as Solved" is for when a solution has been arrived at, not for trying to remove yourself from the discussion. I can understand that you possibly felt frustrated with where the thread/s were going, but we were equally frustrated with the poor flow of information from you. I trust that this encourages you to have a think about how you can approach other issues when posting to forums seeking assistance.

[Aussiebear]

Kamran AJ, do you remember this piece of code that Grade 4.2 offered


give it a go :)

Make sure to place this into the "OFFLINE" workbook as the information is going to be copied into it.

Option Explicit

Sub CopyDynamicColumns()
Dim sourceWorkbook As Workbook
Dim offlineWorksheet As Worksheet
Dim sourceWorksheet As Worksheet
Dim lastRow As Long
Dim dynamicColumns() As Variant
Dim col As Variant
Dim i As Integer
Dim destCol As Integer


' Define the columns you want to copy (1-based index)
dynamicColumns = Array(2, 4, 6) ' Change this array to include the columns you want to copy from. It is currently set to copy columns B, D & F


' Set the offline worksheet where you want to paste the data
Set offlineWorksheet = ThisWorkbook.Worksheets("Sheet1") ' Change "Sheet1" to your OFFLINE (Targets) sheet's name


' Open the online workbook
Set sourceWorkbook = Workbooks.Open("https://your_online_excel_file_url_here.xlsx") ' Replace with the online Excel file URL


' Set the source worksheet from which you want to copy the data
Set sourceWorksheet = sourceWorkbook.Worksheets("Sheet1") ' Change "Sheet1" to your ONLINE (Source) sheet's name


' Find the last row with data in the source worksheet
lastRow = sourceWorksheet.Cells(sourceWorksheet.Rows.Count, "A").End(xlUp).Row


' Loop through each column you want to copy
destCol = 1
For Each col In dynamicColumns
' Copy the column from the source worksheet
sourceWorksheet.Range(sourceWorksheet.Cells(1, col), sourceWorksheet.Cells(lastRow, col)).Copy


' Paste the copied data into the offline worksheet
offlineWorksheet.Cells(1, destCol).PasteSpecial xlPasteValues


' Move to the next destination column
destCol = destCol + 1
Next col


' Close the online workbook without saving changes
sourceWorkbook.Close SaveChanges:=False


' Clean up
Application.CutCopyMode = False
Set sourceWorkbook = Nothing
Set offlineWorksheet = Nothing
Set sourceWorksheet = Nothing
End Sub


What was wrong with it?

[Aussiebear]

Give this a try



Option Explicit

Sub CopyDynamicColumns()
Dim sourceWorkbook As Workbook
Dim offlineWorksheet As Worksheet
Dim sourceWorksheet As Worksheet
Dim lastRow As Long
Dim dynamicColumns() As Variant
Dim col As Variant
Dim i As Integer
Dim destCol As Integer

' Define the columns you want to copy (1-based index)
dynamicColumns = Array(2, 3, 4, 5, 6, 7, 8, 10)

' Set the destination worksheet where you want to paste the data
Set offlineWorksheet = Workbooks("Template B").("Sheet1")

' Open the online workbook
Set sourceWorkbook = Workbooks.Open("https://your_online_excel_file_url_here.xlsx") ' Replace with the online Excel file URL

' Set the source worksheet from which you want to copy the data
Set sourceWorksheet = Workbooks("Template A").("sheet1")

' Find the last row with data in the source worksheet
lastRow = sourceWorksheet.Cells(sourceWorksheet.Rows.Count, "A").End(xlUp).Row

' Loop through each column you want to copy
destCol = 1
For Each col In dynamicColumns

' Copy the column from the source worksheet
sourceWorksheet.Range(sourceWorksheet.Cells(1, col), sourceWorksheet.Cells(lastRow, col)).Copy

' Paste the copied data into the offline worksheet
offlineWorksheet.Cells(1, destCol).PasteSpecial xlPasteValues

' Move to the next destination column
destCol = destCol + 1
Next col

' Close the online workbook without saving changes
sourceWorkbook.Close SaveChanges:=False
' Clean up
Application.CutCopyMode = False
Set sourceWorkbook = Nothing
Set offlineWorksheet = Nothing
Set sourceWorksheet = Nothing
End Sub[

[Aussiebear]

I suspect this will fail as the destination columns will need to be (1, 3, 5, 6, 7, 8, 9 10)